is there a group_concat function in ms-access or something similar?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
You should ask yourself if you need a generic solution (another is by Allen Browne) or if you need it just for the present purpose. If you really only need it this once, do it the easy way.
On a side note, when concatenating lists in VBA code, take advantage of a trick taught to me by long-time Access guru Trevor Best, and that’s to stick the delimiter at the beginning of every value and then use Mid() to strip it off. Instead of this inside your loop through the child records:
If Len(strOutput) = 0 Then strOutput = NewValue Else strOutput = strOutput & ", " & NewValue End If
…use this inside the loop:
strOutput = strOutput & ", " & NewValue
…and then when you exit the loop, strip off the leading delimiter:
strOutput = Mid(strOutput, 3)
This has implications all over the place and simplifies code for concatenation in a whole host of contexts.
There’s an access function to group multiple values into one value (a custom aggregate, I guess.) The link is http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName=’Generic%20Function%20To%20Concatenate%20Child%20Records’
but the site is down for now. If you google the href, you’ll find lots of referneces and examples.
I found this post by Duane Hookum (a Microsoft MVP) that claims to be able to do what you want. I have not tested it though.
By the way, in case you are interested, this is how I found it:
Then I searched again after the content that the answer was attempting to link to, and found it: site:http://www.rogersaccesslibrary.com/ concatenate.
No. Access does not have a GROUP_CONCAT function. However, it is possible to create a VBA function which will let you pass a string containing a SQL statement and get the equivalent functionality (not that I’d recommend it but it is possible).
Taking my own personal wayback machine, here is some code I wrote back when dinosaurs ruled the Earth:
Public Function ListQuery(SQL As String _ , Optional ColumnDelimiter As String = " " _ , Optional RowDelimter As String = vbCrLf) As String 'PURPOSE: to return a combined string from the passed query 'ARGS: ' 1. SQL is a valid Select statement ' 2. ColumnDelimiter is the character(s) that separate each column ' 3. RowDelimiter is the character(s) that separate each row 'RETURN VAL: 'DESIGN NOTES: Const PROCNAME = "ListQuery" Const MAXROWS = 100 Const MAXCOLS = 10 Dim oConn As ADODB.Connection Dim oRS As ADODB.Recordset Dim oField As ADODB.Field Dim sRow As cString Dim sResult As cString On Error GoTo ProcErr Set sResult = New cString Set sRow = New cString Set oConn = GetADOConn() sResult.Clear Do Until oRS.EOF sRow.Clear For Each oField In oRS.Fields With sRow If .Length > 0 Then .Append ColumnDelimiter End If .Append Nz(oField.Value) End With Next oField sRow.Trim If sRow.Length > 0 Then With sResult .Append sRow .Append RowDelimter End With End If oRS.MoveNext Loop oRS.Close oConn.Close With sResult If .Right(Len(RowDelimter)).Value = RowDelimter Then .Length = .Length - Len(RowDelimter) End If End With FunctionResult: ListQuery = sResult.Value CleanUp: Set sResult = Nothing Set sRow = Nothing Set oField = Nothing Set oRS = Nothing Set oConn = Nothing Exit Function ProcErr: ' logging code... Resume CleanUp End Function
GetADOConn function is a centralized function to retrieve the current database connection.
cString is a class that mimics the behavior of .NET’s
StringBuilder class but was written long before .NET was anything other than a TLD and marketing hype. Since this is getting called on every row, VBA’s built-in string concatenation will be slow and thus something like a
StringBuilder class is needed. The original code (which I’ve partially modified) had a cap on the number of rows and columns that could be used which is what the constants are all about.