Access/VBA - How to output data from a column as comma separated string

In online forums or newsgroups dealing with Microsoft Access I frequently read questions about how to get the data from one column in a table or query into a comma (or similar) separated list of values in a string. So basically the rows of one column should be transformed to a string, with the values separated by a delimiter. - Instead of writing an individual answer over and over again, I rather try to write a really good one here, and point to it, if the question arises again. - So here we go...

Let?s say you have got a table with a column containing names (actor's names in this sample). Like this one:

You can solve this problem quite easily with a bit of Access and VBA programming experience. You open a recordset with a query to your table (or query) column. Then, while looping through all the records, you concatenate the values with the desired delimiter in a string and finally return the concatenated string minus the last appended delimiter.

Public Function QueryFieldAsSeparatedString(ByVal fieldName As String, _ ByVal tableOrQueryName As String, _ Optional ByVal criteria As String = "", _ Optional ByVal sortBy As String = "", _ Optional ByVal delimiter As String = ", " _ ) As String ' Paramter description ' fieldName = Is the name of the field containing the values ' we want in our comma separated string ' tableOrQueryName = Is the name of table or query containing the column ' criteria = The criteria to filter the data ' sortBy = An optional sort expression to sort the data ' delimiter = The delimiter used to separate the values. It defaults ' to a comma and a blank, but you can use anything you ' like there Dim db As DAO.Database Dim rs As DAO.Recordset Dim sql As String Dim whereCondition As String Dim sortExpression As String Dim retVal As String Set db = CurrentDb ' If there where any criteria passed to the function, we build a WHERE-condition for SQL If Len(criteria) > 0 Then whereCondition = " WHERE " & criteria End If ' If there was a sort expression passed to the function, we build a ORDER BY for SQL If Len(sortBy) > 0 Then sortExpression = " ORDER BY " & sortBy End If ' building the complete SQL string sql = "SELECT " & fieldName & " FROM " & tableOrQueryName & whereCondition & sortExpression ' opening a recordset Set rs = db.OpenRecordset(sql, dbOpenForwardOnly, dbReadOnly) Do Until rs.EOF ' here we are looping through the records and, if the value is not NULL, ' concatenate the field value of each record with the delimiter If Not IsNull(rs.Fields(0).Value) Then retVal = retVal & Nz(rs.Fields(0).Value, "") & delimiter End If rs.MoveNext Loop ' we cut away the last delimiter retVal = Left(retVal, Len(retVal) - Len(delimiter)) ' setting the return value of the function QueryFieldAsSeparatedString = retVal ' cleaning up our objects rs.Close Set rs = Nothing Set db = Nothing End Function

You can call this function from the Immediate Pane of the VBA-Editor and the output will be printed right below.

The function suports filtering and sorting as well. The following screenshot displays only actors with a rating of 4 or more and sorts it alphabetically. In addition to that we use a dash as separator instead of a comma.

If you put the call to the function in the ControlSource-Property of a textbox control, it will produce the output shown in the screenshot at the beginning of this article.

If you are using DAO for data access there is nothing to add to this. However if you are working with a SQL Server backend and using ADO for data access there might be some other factors to consider. I deal with them in this article describing how to create a comma separated string from an ADO-Recordset in a client-server-scenario using the Clone-, Filter- and Sort- Method/Properties of the ADO-Recordset.

Access/VBA - How to output data from a column as comma separated string

Subscribe to my newsletter