Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Combine Multiple Records Into A Single Delimited Field

Posted by: truittb Jun 1 2006, 08:03 AM

This function will concatenate a single field's value from multiple records into a single delimited string. It uses the Join() function to combine the string. Modify the SQL and or the delimiter to suit your needs.
!--c1-->

CODE
Public Function fMakeCommaDelimited() As String
Dim strTemp() As String
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim i As Long
Dim lngRecCount As Long
Set rs = New ADODB.Recordset
strSQL = "SELECT User_ID From YourTable;" 'Change the fieldname and table name to yours.
With rs
    .ActiveConnection = CurrentProject.Connection
    .Source = strSQL
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open options:=adCmdText
    lngRecCount = .RecordCount
    ReDim strTemp(lngRecCount - 1)
    Do Until .EOF
        strTemp(i) = !User_ID
        .MoveNext
        i = i + 1
    Loop
    .Close
End With
Set rs = Nothing
fMakeCommaDelimited = Join(strTemp, ",")
End Function

Posted by: IvanovSerg Feb 15 2017, 06:28 AM

'use DAO

Public Function fMakeCommaDelimited() As String
Dim strTemp, i
Dim strSQL As String

strSQL = "SELECT User_ID From YourTable;" 'Change the fieldname and table name to yours.
strTemp = CurrentProject.Connection.Execute(strSQL).GetRows
For i = 0 To UBound(strTemp, 2)
fMakeCommaDelimited = fMakeCommaDelimited & strTemp(0, i) & ","
Next
fMakeCommaDelimited = Mid(fMakeCommaDelimited, 1, Len(fMakeCommaDelimited) - 1)

End Function