UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 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
 
IvanovSerg
post Feb 15 2017, 06:28 AM
Post#2



Posts: 1
Joined: 7-October 14



'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
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 11:44 AM