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?

 

[drew8631]

Thanks Joe that's exactly what I needed...Your awesome!!!

It exported the file into the Access Folder on my desktop.
Is that the default location? What would be needed to change that location?

Thanks again,

Andrew

 

[Joe4]

It exported the file into the Access Folder on my desktop.
Is that the default location? What would be needed to change that location?

If you use the code I posted, you can control exactly where you want it to go. Note the following line in my command button code:

Call MyWriteList(txtFullFileName, myCombinedString)

txtFullFileName is a text box on my Form where I can enter the full path and file name of the file I want to correct.
You can do the same, or replace this part of the argument with a variable or static value.

 

[Norie]

If you can get the fields into an array you could also use the Join function.

Don't know if that helps but I didn't see it mentioned.

 

[drew8631]

Thanks!

I will be looking into this and will post back.

 

[james_lankford]

not sure if this is what you're looking for, but it might work for results with few rows
but with 10 of 1000's of rows the string probably gets too large

Sub xxx()
    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connection_string As String
    Dim rs_as_string As String
    
    connection_string = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source="
    connection_string = connection_string & "C:\Users\jamesl\Documents\db1.mdb;"
    
    Set conn = New ADODB.Connection
    
    conn.Open (connection_string)
    
    ' select double quote single quote double quote ampersand EmployeeName ampersand double quote single quote double quote from asdf
    ' this returns EmployeeName surrounded by single quotes
    ' like this 'James'
    Set rs = conn.Execute("select ""'"" & EmployeeName & ""'"" from asdf")
    ' now put a comma space at the end of each row
    ' so you get 'James', 'John', 'Bill',
    rs_as_string = rs.GetString(, , , ", ")
    
    rs.Close
    conn.Close
    
    ' get rid of the last comma and space
    rs_as_string = Left(rs_as_string, Len(rs_as_string) - 2)
    
    Debug.Print rs_as_string
    
End Sub