sql - is there a group_concat function in ms-access?

Question

is there a group_concat function in ms-access or something similar?

Answer 1

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.