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.
Concatenate a List into a Single Field (Column)
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;
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