INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!
Students Click Here

Join Us!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Tek-Tips Posting Policies

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL > FAQs

Microsoft: Access Queries and JET SQL FAQ

How To

How to concatenate multiple child records into a single value by dhookom

faq701-4233

Posted: 28 Sep 03 (Edited 30 Jun 14)

Note:
- Updated June 30, 2014 to correct some errors
- New addition to this function is the ability to change the final delimiter to get a return like red, white, and blue.

To use any function like this, open a new module. Copy the code from "Function Con..." to "End Function" into the new module. Select Debug|Compile to make sure there are no compile errors. Then save the module as "modConcatenate". You can then use the Concatenate() function as an expression in a query or control source or other places.

Note: When creating a query with this function, make sure you understand the difference between numeric and text fields as noted in the code.

While this sample uses two tables, it could also be used with a single table. It might be easiest to use a totals query to create a pseudo "tblFamily" that is grouped on the appropriate field.

CODE

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ", _
        Optional pstrLastDelim As String = "") _
        As Variant
'   Created by Duane Hookom, 2003
'   Modified 6/30/2014 to correct some issues
'   this code may be included in any application/mdb providing
'   this statement is left intact
'   example
'   tblFamily with FamID as numeric primary key
'   tblFamMem with FamID, FirstName, DOB,...
'   return a comma separated list of FirstNames
'   for a FamID
'    John, Mary, Susan

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID]) as FirstNames
'   FROM tblFamily
'   ============================================

'   to get a return like Duane, Laura, Jake, and Chelsey

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID], ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================

'   If FamID is a string rather than numeric,
'    it will need to be delimited with quotes

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================


'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intCount As Integer
    Dim strLastValue As String
    Dim intLenB4Last As Integer     'length before last concatenation
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    'Dim rs As New ADODB.Recordset
    'rs.Open pstrSQL, CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic    
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                intCount = intCount + 1
                intLenB4Last = Len(strConcat)
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                strLastValue = .Fields(0)
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========    
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
        If Len(pstrLastDelim) > 0 And intCount > 1 Then
            strConcat = Left(strConcat, intLenB4Last - Len(pstrDelim)) & pstrLastDelim & strLastValue
        End If
    End If
    If Len(strConcat) > 0 Then
        Concatenate = strConcat
     Else
        Concatenate = Null
    End If
End Function

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

My FAQ Archive