Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Combine Fields from Multiple Records into One Long String

This is a discussion on Combine Fields from Multiple Records into One Long String within the Microsoft Access forums, part of the Question Forums category; I have an Excel macro that I wrote years ago that will take all the values from a certain column ...

  1. #1
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,353

    Default Combine Fields from Multiple Records into One Long String

    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:

    joe@abc.com
    tom@abc.com
    jill@xyz.com

    to something like this:
    joe@abc.com;tom@abc.com;jill@xyz.com

    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”)

    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).

    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?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Location
    California
    Posts
    576

    Default Re: Combine Fields from Multiple Records into One Long String

    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
    Imagine a funny saying here.

  3. #3
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,353

    Default Re: Combine Fields from Multiple Records into One Long String

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Location
    California
    Posts
    576

    Default Re: Combine Fields from Multiple Records into One Long String

    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.

    Code:
    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.

    Code:
    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

    Code:
    call writelist("testorama.txt")
    hth,

    Rich
    Imagine a funny saying here.

  5. #5
    Board Regular
    Join Date
    Jul 2008
    Location
    Cleveland, OH
    Posts
    636

    Default Re: Combine Fields from Multiple Records into One Long String

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

    Code:
    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
    Last edited by gauntletxg; Aug 3rd, 2010 at 09:28 PM.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,353

    Default Re: Combine Fields from Multiple Records into One Long String

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular Marbles's Avatar
    Join Date
    Feb 2005
    Posts
    571

    Default Re: Combine Fields from Multiple Records into One Long String

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

    http://bytes.com/topic/access/answer...mn-into-string

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,353

    Default Re: Combine Fields from Multiple Records into One Long String

    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:
    Code:
     
    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:
    Code:
     
    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:
    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!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    New Member
    Join Date
    Apr 2011
    Posts
    3

    Default Re: Combine Fields from Multiple Records into One Long String

    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

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,353

    Default Re: Combine Fields from Multiple Records into One Long String

    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?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com