Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to concatenate multiple child records into a single value

How To

How to concatenate multiple child records into a single value

by  dhookom  Posted    (Edited  )

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.

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

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
[color #4E9A06]'   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 =======[/color]
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intCount As Integer
    Dim strLastValue As String
    Dim intLenB4Last As Integer     [color #4E9A06]'length before last concatenation[/color]
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)

[color #4E9A06]'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    'Dim rs As New ADODB.Recordset
    'rs.Open pstrSQL, CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic[/color]    
    Dim strConcat As String [color #4E9A06]'build return string[/color]
    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
[color #4E9A06]'====== uncomment next line for DAO ========[/color]    
    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
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top