Login or Sign Up to become a member!

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

From Wiki

It is not usually a good idea to concatenate rows, but it is probably the easiest way to get a report that shows a delimited list of items, such as attendees.

The code sample below will return a delimited list from either SQL or string input. This is how to use it in a query:

   SELECT documents.MembersOnly, 
       ConcatList("SELECT FName From Persons WHERE Member=True",":") AS Who, 
       ConcatList("",":","Mary","Joe","Pat?") AS Others
   FROM documents;

Or

   SELECT tc.Company, 
          ConcatList("SELECT Contract 
                      FROM tblservices 
                      WHERE CompanyKey=" & tc.[CompanyKey],", ") AS Contracts
   FROM tblCompanies tc

Update: A new version using ADO

This is faster and easier, but a lot more sensitive about field (column) names. You need to ensure that they are not reserved words. Another advantage of the ADO version is that you are not limited in the number of columns to be concatenated:

   SELECT d.DeptID, d.Department, 
          ConcatADO("SELECT FName & ' ' & SName, Address FROM Persons 
                     WHERE DeptID=" & [d].[DeptID],", "," : ") AS Who
   FROM Departments AS d INNER JOIN Persons AS p ON d.DeptID = p.DeptID
   GROUP BY d.DeptID, d.Department, 3;

   Function ConcatADO(strSQL As String, strColDelim, strRowDelim, ParamArray NameList() As Variant)
   Dim rs As New ADODB.Recordset
   Dim strList As String
   
   On Error GoTo Proc_Err
   
       If strSQL <> "" Then
           rs.Open strSQL, CurrentProject.Connection
           strList = rs.GetString(, , strColDelim, strRowDelim)
           strList = Mid(strList, 1, Len(strList) - Len(strRowDelim))
       Else
           strList = Join(NameList, strColDelim)
       End If
   
       ConcatADO = strList
   
   Exit Function
   
   Proc_Err:
       ConcatADO = "***" & UCase(Err.Description)
   End Function

This is the original version using DAO

   Function ConcatList(strSQL As String, strDelim, ParamArray NameList() As Variant)
   'Reference: Microsoft DAO x.x Object Library
   Dim db As Database
   Dim rs As DAO.Recordset
   Dim strList As String
   
   Set db = CurrentDb
   
   If strSQL <> "" Then
       Set rs = db.OpenRecordset(strSQL)
   
       Do While Not rs.EOF
           strList = strList & strDelim & rs.Fields(0)
           rs.MoveNext
       Loop
   
       strList = Mid(strList, 1, Len(strList) - Len(strDelim))
   Else
   
       strList = Join(NameList, strDelim)
   End If
   
   ConcatList = strList
   
   End Function