X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Combine Multiple Records Into A Single Delimited Field    
post Jun 1 2006, 08:03 AM

Retired Moderator
Posts: 13,587
Joined: 23-June 02
From: Texas (Is there anywhere else?)

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.
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
        i = i + 1
End With
Set rs = Nothing
fMakeCommaDelimited = Join(strTemp, ",")
End Function
Go to the top of the page

Posts in this topic

Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 02:40 PM