Code Snippets

In all the times I have spent participating in forum discussions about all sorts of users' questions, I have learnt a great deal from all the participants.

I have decided to post some of the sample codes that I found users were asking for almost all the time to help me keep them in one place and to make it easier for me to share them again and again.

Hope you find them useful.



SimpleCSV()

Here's a simple function to concatenate all the records of a select query into a delimited string. Just pass a "SELECT" SQL statement with only one field (the field you want to concatenate) to the function and an optional delimiter. For example:

SimpleCSV("SELECT CustomerName FROM tblCustomers WHERE CompanyID=" & [CompanyID])

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

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

Update:

A very good friend at UtterAccess recommended that I also mention other possible uses of this simple function in case it might be of benefit to others. Although SimpleCSV() was originally written to concatenate "child" records together, there may be instances that the user just wants to concatenate "all" the records in a table or query.

If that's the case, the user doesn't really need to construct a complete SQL statement to use this function. Instead, passing the name of the query or table as a string argument should suffice. However, it is very important to remember that the function assumes that the field to concatenate is always the "first" one in the list of fields. Since that would be the ID field for most tables, the user would probably tend to use a query most of the times, unless he or she was really trying to concatenate the ID values together.

One other point to make about this function is that although it will only concatenate one field from each record together, it is still possible to concatenate multiple fields from the original table or query before the function combines all those values together for all records. For example, SimpleCSV() can easily concatenate all customers' last names for a particular sales agent, but what if you want to list both first and last names in that list, and you happen to store them in separate fields? The answer to that question is that you simply concatenate the two fields together in your SQL statement before passing it as an argument to SimpleCSV(). In other words, maybe something like this:

Customers: SimpleCSV("SELECT [LastName] & ', ' & [FirstName] FROM 
tblCustomers WHERE SalesPersonID=" & [SalesPersonID], "/")