473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Field Concatenation string returns number instead of data

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.

5 1287
NeoPa
32,556 Expert Mod 16PB
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
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
5,501 Expert Mod 4TB
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
I inherited the database, but that does sound like a good idea... will try to implement it.

Thanks
Jun 23 '15 #5
NeoPa
32,556 Expert Mod 16PB
It certainly is a good idea Ed.
Jun 25 '15 #6

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

Similar topics

5
by: Ernez Catovic | last post by:
Please help me. I can see the content of field type CHAR(n) FOR BIT DATA of DB2 database, but when I try to see the content of mentioned field type by using SQL query (ODBC connection) only I...
6
by: kobu.selva | last post by:
I was recently part of a little debate on the issue of whether constants and string literals are considered "data objects" in C. I'm more confused now than before. I was always under the...
2
by: kathy | last post by:
what is the better way to determine if a string is number or not?
2
by: GY2 | last post by:
How do I refer to controls by a string variable name instead of by integer item index? A form contains 7 rich textbox controls labeled rtb1-rtb7. In VB6 I could use the following code to loop...
4
by: dba_222 | last post by:
Dear Experts, Ok, I hate to ask such a seemingly dumb question, but I've already spent far too much time on this. More that I would care to admit. In Sql server, how do I simply change a...
3
by: jdvon | last post by:
Reports and Queries return auto ID number instead record itself Access 2002 when I run a report or a query they result in the auto ID number (in some cases) rather than the value iteslf. What...
35
by: dtschoepe | last post by:
Greetings. I am working on an assignment and can't seem to get the right concept for something I'm attempting to do with enum data types. I have defined the following in my code: enum color...
3
by: palani12kumar | last post by:
hi. I want to get a string as input. If the input is an integer i want to save it to an int variable. I know to convert a number to int. but how to convert a string to number(integer). in C++
3
by: Ameet Nanda | last post by:
Hi All, I access net using a proxy, which I have to authenticate everytime I try to access net from my system. Now when I use urllib2.urlopen(url) , I cant get ahead. I must provide proxy...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.