is there a group_concat function in ms-access or something similar?
GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.
MySQL | Group_CONCAT() Function. The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With