Combine Fields from Multiple Records into One Long String

[Joe4]

I have an Excel macro that I wrote years ago that will take all the values from a certain column on a spreadsheet and concatenate them into one long string, with text qualifiers and delimiters of my choosing. It could convert this:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
joe@abc.com
tom@abc.com
jill@xyz.com
<o> </o>
to something like this:
joe@abc.com;tom@abc.com;jill@xyz.com
<o> </o>
I use this for two distinct purposes:
1. Combine a list of email addresses I want to send something to;
2. Use to build criteria for a SQL statement (i.e. using “IN”)
<o> </o>
A client of mine has an Access database that has a Contact table that contains an email address field. On occasion, they would like to email a subset of that table (can easily select which records with a query). Currently, they export the results of the query to Excel and use my macro to create the email address list. I would like to make this more dynamic, and bypass the Excel step altogether, i.e. have Access combine the list (and maybe export to single record text file).
<o> </o>
I imagine it is going to require VBA (which I have done a lot of), but I haven’t ever really tried combining field values from multiple records down into a single record. Does anyone have any good code links or tips?

 

[revans]

I answered something like this for a list box a while back. You'd have to change the code to open up a recordset returning the column you want then loop through it building your string.

hth,

Rich

 

[Joe4]

Well, its a bit different, as that link combines multiple fields within a single record, whereas I am combining a single field from multiple records.

In the the instance you worked on, you can get away with a basic User Defined Function instead of having to use RecordSets. I admit that RecordSets are not my forte, by I think that part I can probably work my way through. The more difficult part is using VBA to write the results to a single record text file. I hardly ever try to create text files directly from Access VBA.

I was just wondering/hoping if someone had already done something like this before I go ahead and struggle through it and recreate the wheel here.

 

[revans]

Try this. You'll want to make change the sql variable to return the column you want and the !Myfield to the name of the field.

Public Function MakeList() As String
Dim rst As adodb.Recordset
Dim sql As String
Dim temp As String
sql = "SomeQueryThatListsTheRecordsYouWant"
Set rst = New adodb.Recordset
With rst
  .Open sql, CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
  If Not (.EOF And .BOF) Then
    .MoveFirst
    Do
      temp = temp & ";" & ![MyField]
      .MoveNext
    Loop Until .EOF
  End If
  .Close
End With
Set rst = Nothing
If Len(temp) > 0 Then 'ditch the first and last delimiter
  temp = Mid(temp, 2, Len(temp) - 1)
End If
MakeList = temp
End Function

There are various ways to write a text file to disc. I like the filesystemobject. So you could use this.

Public Sub WriteList(strFileName As String)
'needs reference to Microsoft Scripting Runtime
 Dim fso As New FileSystemObject
Dim ts As TextStream
Set ts = fso.CreateTextFile("C:\" & strFileName)
  ts.Write (MakeList)
Set ts = Nothing
Debug.Print "done"
End Sub

You could call it from the immediate window (or however you like) like this

call writelist("testorama.txt")

hth,

Rich

 

[gauntletxg]

Something like this maybe? You can pass your query and the field you want to combine to the function

Function CreateList(strSource As String, strField As String, strDelim As String) As String
Dim db As Database, rs As Recordset, temp As String, i As Long

On Error Resume Next
i = DCount(strField, strSource)
On Error GoTo 0
If i > 0 And Not Err Then
    Set db = CodeDb
    Set rs = db.OpenRecordset(strSource, dbOpenSnapshot, dbReadOnly, dbReadOnly)
    With rs
        .MoveFirst
        Do Until .EOF
            temp = temp & rs(strField) & strDelim
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End If

If temp <> vbNullString Then temp = Left(temp, Len(temp) - 1)
CreateList = temp
End Function

edit: pretty much the same thing as revans

 

[Joe4]

Thanks to the both of you.
I hope to get some time to start playing around with it later today. I'll let you know how it goes.

 

[Marbles]

I haven't tried this, but it might be what you're looking for.

http://bytes.com/topic/access/answers/882945-concatenate-field-column-into-string

 

[Joe4]

Thanks Marbles, that code is pretty much along the same lines as the other two above.

Building the string was easier than expected, I just wasn't sure about writing it out to a file (just because I have never written out to a text file before directly from Access VBA without using TransferText, TransferSpreadsheet, etc).

Borrowing from the posts made, I was able to create something to work for me. I added an additional argument for a Text Qualifer, and use DAO recordsets (just because that is what I am used to). Here is what I came up with.

First the code to build the string:

Function CombineList(strSource As String, strField As String, strDelim As String, Optional strTextQual As Variant) As String
'   Combines a single column from multiple records into a single string
'   Arguments
'       strSource = name of query or table
'       strField = name of field you want to combine
'       strDelim = delimiter to use to separate each value
'       strTextQual = text qualifer to use around each field (optional)
 
    Dim myDB As DAO.Database
    Dim myRS As DAO.Recordset
    Dim myRecCount As Long
    Dim myTempString As String
    
    Set myDB = CurrentDb
    Set myRS = myDB.OpenRecordset(strSource, dbOpenDynaset)
    
    myRecCount = DCount(strField, strSource)
    If myRecCount > 0 Then
        With myRS
            .MoveFirst
            Do While Not .EOF
                myTempString = myTempString & strTextQual & myRS(strField) & strTextQual & strDelim
                .MoveNext
            Loop
        End With
    End If
    Set myRS = Nothing
    Set myDB = Nothing
    If Len(myTempString) > 0 Then CombineList = Left(myTempString, Len(myTempString) - 1)
    
End Function

Next, the code to write out the text file:

Public Sub MyWriteList(strFullFileName As String, strExportString As String)
'   Export a built string to a text file
'   (include reference to Microsoft Scripting Runtime)
     
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Set ts = fso.CreateTextFile(strFullFileName)
    ts.Write (strExportString)
    Set ts = Nothing
    MsgBox "File exported to:" & vbCrLf & strFullFileName
    
End Sub

Now, to run it all, I created a Form with 5 text boxes where they can enter the 4 arguments for my function, and the export file name for the last procedure. I then added a Command Button to the Form to run it all, which has the following VBA code:

Private Sub cmdCreateFile_Click()
'   Creates a text file that combines all records from a single field into one line with delimiters and text qualifiers
 
    Dim myCombinedString As String
    
'   Using four text boxes on form, build string
    myCombinedString = CombineList(txtQuery, txtField, txtDelimiter, txtTextQual)
    
'   Write out to file, using string calculated above and file name from text box on form
    Call MyWriteList(txtFullFileName, myCombinedString)
 
End Sub

Everything appears to work well.

Thanks for all the help!

 

[drew8631]

Hi Guys,

I'm trying to write a similar code for my access project.
I understand that you first have to build the string and then use some method to export it to a text file or where ever you want it to go.
Looking through this thread, I've found several different codes to create the string which seems to work fine. I simply copied and pasted the vb code into a module in access.
The problem I'm having is getting the string to export to a text file.
Joe4's code would work perfect for me, but I can't figure out how to "include reference to Microsoft Scripting Runtime".
I'm guessing what I'm looking for is some code I can stick into a command button on a form to export my created string into a text file.

Your help is greatly appreciated.

Thanks,

Andrew

 

[Joe4]

Joe4's code would work perfect for me, but I can't figure out how to "include reference to Microsoft Scripting Runtime".

If you go into the VB Editor, go to the Tools menu and select References. From there, scroll down the list and find "Microsoft Scripting Runtime" and check that box.

Is that what you are looking for?