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

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

   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, Len(strDelim) + 1)
   Else
   
       strList = Join(NameList, strDelim)
   End If
   
   ConcatList = strList
   
   End Function

Acknowledgements to Duane Hookom