I found the following code on ee
However, when I modify it for my needs it is returning the same value for every row.
I need to look at an WS_NO_UO and group together every instances of the MN into the same field.
Why is not moving to the next record of the APNo and returning its list of MN?
here is my code - KEEP IN MIND THAT I USE GLOBAL DECLARATIONS FOR MOST OF MY VARIABLES.
I also need to be able to handle empty values that will not return the following example:
, , , , , , , 123435, 5435665, 24342,
Also hide the last comma.
Public Function Concat(WSNO As String) As String
Dim strTopic As String
StrSQL = "SELECT APNo, WS_NO_UO, MN" & _
" FROM Qry_PrgUODetail" & _
" GROUP BY APNo, WS_NO_UO, MN" & _
" ORDER BY WS_NO_UO"
' " WHERE WS_NO_UO = " & Chr(34) & gWSNO & Chr(34) & "" & _
Set rs = CurrentDb.OpenRecordset(StrSQL)
Do Until rs.EOF
strTopic = strTopic & rs![MN] & ", "
If Len(strTopic & "") > 0 Then
strTopic = Left(strTopic, Len(strTopic) - 2)
strTopic = ""
Concat = strTopic