By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Concatenate a field column into a string

P: 4
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
Mar 8 '10 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,634
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fConcatEMailAddr()
  2. Dim MyDB As DAO.Database
  3. Dim rstEAddr As DAO.Recordset
  4. Dim strBuild As String
  5.  
  6. Set MyDB = CurrentDb
  7. Set rstEAddr = MyDB.OpenRecordset("Table1", dbOpenForwardOnly)
  8.  
  9. With rstEAddr
  10.   Do While Not .EOF
  11.     If ![EMail_Addr] <> "" Then
  12.       strBuild = strBuild & ![EMail_Addr] & ";"
  13.     End If
  14.       .MoveNext
  15.   Loop
  16. End With
  17.  
  18. rstEAddr.Close
  19. Set rstEAddr = Nothing
  20.  
  21. fConcatEMailAddr = Left$(strBuild, Len(strBuild) - 1)
  22. End Function
Mar 8 '10 #2

P: 4
ADezii:

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

Ron
Mar 8 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.