UtterAccess.com
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    
 
   
truittb
post Jun 1 2006, 08:03 AM
Post#1


Retired Moderator
Posts: 13,563
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.
!--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
Go to the top of the page
 

Posts in this topic



Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 05:52 AM