How Can I Concatenate Lists in MS Access Without a `group_concat` Function?

Concatenating Lists in MS Access

MS Access doesn't natively offer a group_concat function like in other databases. However, you can employ various techniques to achieve similar functionality.

One option is to use VBA code. If you only need it for a specific purpose, a quick and easy approach is to simply concatenate the list items using the & operator. The code snippet below demonstrates this method:

Dim strOutput As String

For Each record In rs
    If Len(strOutput) = 0 Then
        strOutput = record.Item("Value")
    Else
        strOutput = strOutput & ", " & record.Item("Value")
    End If
Next

If you need a more generic solution, you can utilize the trick suggested by Access expert Trevor Best. Instead of adding the delimiter at the end of each value, append it to the beginning. Then, use the Mid() function to remove it:

strOutput = "delimiter" & NewValue

Finally, strip the leading delimiter when exiting the loop:

strOutput = Mid(strOutput, 3)

This technique simplifies concatenation in various scenarios within VBA code.

The above is the detailed content of How Can I Concatenate Lists in MS Access Without a `group_concat` Function?. For more information, please follow other related articles on the PHP Chinese website!