The following is just a simple function most users and developers can add to their database to quickly produce a delimited string of records they would like to combine. I purposely made it simple because all the bells and whistles, such as sorting the list or accepting listboxes as arguments, are already available on other websites, so there's really no need for me to reinvent the wheel for those.
To use this function, just pass a "SELECT" SQL statement and an optional delimiter to it. The power of this simple function is really in the creativity of your SQL statement. Please note there are no error handlers, so you'll have to add your own.
As always, I appreciate any comments you may have. Thank you.
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://accessmvp.com/thedbguy 'v1.0 - 8/20/2013 Dim db As DAO.Database Dim rs As DAO.Recordset Dim strCSV As String Set db = CurrentDb() Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) '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 db = Nothing End Function
Original post date: August 21, 2013