Access has no direct equivalent of SQL Server STRING_AGG. A small DAO function can open an ordered snapshot query and join the first field from each row. Use it for reports and small result sets, not as a substitute for normalized storage.
Last updated: September 26, 2026.
Public Function ConcatValues(ByVal sqlText As String, _
Optional ByVal delimiter As String = ", ") As String
Dim rs As DAO.Recordset
Dim result As String
Set rs = CurrentDb.OpenRecordset(sqlText, dbOpenSnapshot)
Do While Not rs.EOF
If Not IsNull(rs.Fields(0).Value) Then
If Len(result) > 0 Then result = result & delimiter
result = result & CStr(rs.Fields(0).Value)
End If
rs.MoveNext
Loop
rs.Close
ConcatValues = result
End FunctionPlace the function in a standard VBA module and ensure the Microsoft Office Access database engine object library is available. The SQL passed to it should come from trusted query logic, not raw user input.
Call the function from a query
A calculated field can call ConcatValues("SELECT TagName FROM ProjectTags WHERE ProjectID=" & [ProjectID] & " ORDER BY TagName"). The ORDER BY is important: without it, row order is not guaranteed. Use a numeric ID in the criterion; text values require quote escaping.
The function skips Null values but retains empty strings. If blanks should disappear, also test Len(Nz(rs.Fields(0).Value, "")) > 0. If duplicate values should appear only once, use a saved SELECT query with DISTINCT.
Know the practical limits
The function opens a recordset for each output row. A report with hundreds of groups can therefore issue hundreds of small queries. Index the foreign key, pre-aggregate data when possible, or move the concatenation to SQL Server when the source is linked. Keep the result within the capacity of the Long Text destination or report control.
Do not store the display string as relational data
A comma-separated list is useful for display, export, or a report. Keep the underlying many-to-many rows in their junction table so they remain searchable and enforceable. Use the appropriate Access join to produce the input rows and handle Null values before presentation.
