INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
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!
*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
|
Microsoft: Access Queries and JET SQL FAQ
How To
How to concatenate multiple child records into a single value by dhookom
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.
CODEFunction 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 |
|
|
|
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close