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

Field Concatenation string returns number instead of data

P: 3
Hi

I'm just getting started with Access and vba and any help would be greatly appreciated.

I am trying to create a string of email addresses from a query called 'Clients' and the field name is 'email'.

I found the code below and however the result is a string of numbers instead of the actual email address in the field.

The result is "213;324;656;222;435;432"
I would like it to return "johndoe@gmail.com;janedoe@gmail.com...."


Here is the code below.

Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2.  Dim rstEAddr As DAO.Recordset
  3.  Dim strBuild As String
  4.  
  5.  Set MyDB = CurrentDb
  6.  Set rstEAddr = MyDB.OpenRecordset("Clients", dbOpenForwardOnly)
  7.  
  8.  With rstEAddr
  9.    Do While Not .EOF
  10.      If ![email] <> "" Then
  11.        strBuild = strBuild & ![email] & ";"
  12.       End If
  13.         .MoveNext
  14.     Loop
  15.   End With
  16.  
  17.   rstEAddr.Close
  18.   Set rstEAddr = Nothing
  19.  
  20.   fConcatEMailAddr = Left$(strBuild, Len(strBuild) - 1)
Jun 22 '15 #1

✓ answered by NeoPa

I would guess that your query [Clients] is returning a field [email] that is defined in your table somewhere as a ComboBox. That is to say that while the displayed value might be the name (or in this case the SMTP Address), the actual stored value is a numeric reference to a record that contains that name.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,769
I would guess that your query [Clients] is returning a field [email] that is defined in your table somewhere as a ComboBox. That is to say that while the displayed value might be the name (or in this case the SMTP Address), the actual stored value is a numeric reference to a record that contains that name.
Jun 22 '15 #2

P: 3
Thanks, for the tip.

Turns out it was actually a number which referring to a different table. I just did a dlookup to find the actual email address.
Jun 23 '15 #3

zmbd
Expert Mod 5K+
P: 5,397
Instead of using the DLookup() why not create a query, or modify the [Clients] query, to return your emails and use that in the code above? You already have the recordset open.
Jun 23 '15 #4

P: 3
I inherited the database, but that does sound like a good idea... will try to implement it.

Thanks
Jun 23 '15 #5

NeoPa
Expert Mod 15k+
P: 31,769
It certainly is a good idea Ed.
Jun 25 '15 #6

Post your reply

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