Concatenate a field column into a string

[RonMcIntire]

I wish to concatenate a column of email addresses in an access table or query into one string so I can cut and paste the string into the TO: field in an email. I'm using Access 2003.

Can it be done in a query? How do I do it with Access SQL?

Thanks,

Ron

[ADezii]

Assuming your Table Name is Table1 and the E-Mail Address Field in Table1 is named [EMail_Addr], the following Function will return your concatenated String of E-Mail Addressess, Delimited in the proper manner:

Public Function fConcatEMailAddr()
Dim MyDB As DAO.Database
Dim rstEAddr As DAO.Recordset
Dim strBuild As String

Set MyDB = CurrentDb
Set rstEAddr = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)

With rstEAddr
  Do While Not .EOF
    If ![EMail_Addr] <> "" Then
      strBuild = strBuild & ![EMail_Addr] & ";"
    End If
      .MoveNext
  Loop
End With

rstEAddr.Close
Set rstEAddr = Nothing

fConcatEMailAddr = Left$(strBuild, Len(strBuild) - 1)
End Function

[RonMcIntire]

ADezii:

Thanks for your input. I will give it a shot.

Ron