Query to string...

[Guest]

I need to take records from a query and place them into a string like this.

From this

email.addy1|Name1
email.addy2|Name2
email.addy3|Name3

To this

"email.addy1,email.addy2,email.addy3"

I'm creating a database that will email once a day a list of items people
are waiting on.

Thanks
Mike

 

[Duane Hookom]

There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane that does
this.

 

[Guest]

This code has no error checking in it but here is what I figured out.
Basically the code reads the field containing the email address from the
first record
and pops it into the strAddress variable and adds the delimeter then loops
through the record set adding the next field and delimeter to one another
then at the end it test the delimeter variable to see its length then trims
that many characters from the end then adds the quotes back to it. Now I
should be able to use this to do automated e-mailing of some sort.

Public Sub codDataTest()
Dim rst As DAO.Recordset, strAddress As String, _
strDelimeter As String, numLengthOfDelimeter As Long
strDelimeter = ";"
numLengthOfDelimeter = Len(strDelimeter)
Set rst = CurrentDb.OpenRecordset("qryDataTest")
rst.MoveLast
rst.MoveFirst
strAddress = rst!strEmailAddress & strDelimeter
rst.MoveNext
Do Until rst.EOF
strAddress = strAddress & rst!strEmailAddress & strDelimeter
rst.MoveNext
Loop
strAddress = Left(strAddress, Len(strAddress) - numLengthOfDelimeter)
strAddress = "'" & strAddress & "'"
'Debug.Print strAddress
'MsgBox strAddress
End Sub

 

[Duane Hookom]

Your code is basically the same as the generic concatenate function. While
you code is good for just one task, the generic function can be used in
other tasks.