A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of this function is it doesn't work with parameter queries.
I have always pointed people to Leigh's Generic Recordset function to deal with parameter queries and avoid the "3061. Too few parameters" error.
Today, I was trying to help someone in the forum and decided to combine the two functions together. The following updated version of the SimpleCSV() function should be able to handle certain parameter queries. Please let me know if you find any bug.
Public Function SimpleCSV(strSQL As String, _
Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records in the SELECT SQL statement
'Source: http://www.accessmvp.com/thedbguy
'v1.0 - 8/20/2013
'v2.0 - 8/25/2021 Handles parameter queries
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Variant
Dim strCSV As String
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("", "SELECT * FROM (" & strSQL & ")")
With qdf
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
End With
Set rs = qdf.OpenRecordset
'Concatenate the first (and should be the only one) field from the SQL statement
With rs
Do While Not .EOF
strCSV = strCSV & strDelim & .Fields(0)
.MoveNext
Loop
.Close
End With
'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
End Function
