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

Concatenate Email field

P: 4
I have a simple Contact Management database. There is a table called 'Contacts' containing a email field. I need to concatenate each record's email field into one text string separated by a semicolon for an email distribution list.

I'm sure this can be done simply with SQL but I haven't been able to get it right.

Can anyone help?

Thanks a TON.

Mar 13 '07 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 159
this is a function I use to do the same thing
Expand|Select|Wrap|Line Numbers
  2. Function ConcRec(Fld As String, Tbl As String, Criteria As String, Optional Dilm)
  3. On Error GoTo ConcRec_err
  4. Dim mySQl As String
  5. Dim myStrg As String
  6. Dim RecRS As New ADODB.Recordset
  7. Dim myDilm As String
  10. If Not IsMissing(Dilm) Then
  11. myDilm = Dilm
  12. Else
  13. myDilm = ", "
  14. End If
  16. mySQl = "SELECT [" & Fld & "] As myFld FROM [" & Tbl & "] WHERE " & Criteria & ";"
  17. RecRS.Open mySQl, CurrentProject.Connection
  19. Do Until RecRS.EOF
  20.     If IsNil(myStrg) Then
  21.     myStrg = RecRS.Fields("myFld")
  22.     Else
  23.     myStrg = myStrg & myDilm & RecRS.Fields("myFld")
  24.     End If
  25.     RecRS.MoveNext
  26. Loop
  28. ConcRec = myStrg
  29. RecRS.Close
  30.     Exit Function
  32. ConcRec_err:
  33. MsgBox Err.DESCRIPTION & vbCr & " - " & Err.Number, vbOKOnly, "Error"
  34. Exit Function
  36. End Function
if you need help getting it to work for you let us know
Mar 13 '07 #2

P: 4
Thanks for the quick response.

Please excuse my ignorance, but could you give me a little more direction on what to do with this? I've put the code into a module, but not sure where to go from there.

Just point me in the right direction.

Some more detail about the dbse:

The table containing the email addy's is called "tblContacts", field name is "Email".

Thanks again.
Mar 15 '07 #3

Expert 100+
P: 159
If youv'e already inserted the function into a module all you need to do is insert it's name and arguments where you want to use it (on a form, report, in a query) just like you would with a built in function like dlookup.
based on the info in your post the expression should look like this.

ConcRec(“Email”, “tblContacts”, “not isnull([email])”, “; “)

with this function I always include the not isnull criteria because I did not build in to the function a method of handling nulls.
Mar 15 '07 #4

P: 4
Ok, I got a compile error as follows: "Compile Error: Sub or Function not defined."

It is highlighting the following code in the module you supplied:

Expand|Select|Wrap|Line Numbers
  1. Do Until RecRS.EOF
  2.     If IsNil(myStrg) Then
  3.     myStrg = RecRS.Fields("myFld")
  4.     Else
  5.     myStrg = myStrg & myDilm & RecRS.Fields("myFld")
  6.     End If
  7.     RecRS.MoveNext
  8. Loop
The "IsNil" on line 2 above (no quotes) is highlighted. What's IsNil? Should that be IsNull?

Thanks for your help.
Mar 15 '07 #5

Expert 100+
P: 159
Yes, Change isnil to isnull and it should work fine.
Mar 15 '07 #6

P: 4
Thanks! Works like champ.
Mar 16 '07 #7

Post your reply

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