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.

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


MSeda
Expert 100+
P: 159
this is a function I use to do the same thing
Expand|Select|Wrap|Line Numbers
  1.  
  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
  8.  
  9.  
  10. If Not IsMissing(Dilm) Then
  11. myDilm = Dilm
  12. Else
  13. myDilm = ", "
  14. End If
  15.  
  16. mySQl = "SELECT [" & Fld & "] As myFld FROM [" & Tbl & "] WHERE " & Criteria & ";"
  17. RecRS.Open mySQl, CurrentProject.Connection
  18.  
  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
  27.  
  28. ConcRec = myStrg
  29. RecRS.Close
  30.     Exit Function
  31.  
  32. ConcRec_err:
  33. MsgBox Err.DESCRIPTION & vbCr & " - " & Err.Number, vbOKOnly, "Error"
  34. Exit Function
  35.  
  36. End Function
  37.  
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

MSeda
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

MSeda
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.