Need to concatenate data from one field

[Guest]

Hello everyone!!! I'm using A02 on XP. Have a table with a field containing
email addresses (470 records). I need to send one email with all of the
addresses in the bcc: line. How do I concatenate the 470 records into one
field (each email separated by a comma)?

For example, my table has:

FName LName EmailAddress
John Doe (e-mail address removed)
Susan Gordan (e-mail address removed)
Sandy Smith (e-mail address removed)

My resulting data should be:

(e-mail address removed),[email protected],[email protected],

Not sure which direction to go on this. Would appreciate any help or advice.

Thanks!

 

[Guest]

Duane Hookom has a good solution --
Generic Concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Guest]

Thanks Karl,

I actually downloaded that Friday and hope to try it this morning. (I found
a couple others there I'm going to check out as well. - Great Website!)

Thanks VERY much for being here to help folks out. I appreciate the info.

 

[Guest]

Hi Karl! Hope you still have the thread on this. Copied in the module and
adjusted the wordage and it works GREAT! Thank you very much for the
referral. Now, a question; I need to say LIKE rather than WHERE and can't
figure it out.

Here's Mr. Hookum's wordage that I used:

Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID])

Mine says:

=Concatenate("SELECT Email FROM ConcatenateEmailsTable")

This works perfect for my project because I want all of the email addresses.

HOWEVER, I want to save this and use it again and I just know I'll need to
say 'WHERE Email LIKE'. I can do the 'WHERE =' but the WHERE LIKE is
throwing me off. So, how would I say WHERE Email LIKE "*.net" in my
statement above.

I've got:

=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE LIKE "
& "*.com")

And I get the error:

Run-time error '-2147217900 (80040e14)': Syntax error (missing operator) in
query expression '[Email] LIKE *.com'.

So I know it's something simple like my quotes. Can you help? I'll wait a
bit to see if you have the thread before posting this out.

Thanks very much for your advice.

 

[Duane Hookom]

All of my samples used numeric values in the WHERE. You must add the quotes
around all text/string values...
Try:
=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE LIKE
'*.com' ")

--
Duane Hookom
MS Access MVP

[QUOTE="Bonnie"]
Hi Karl! Hope you still have the thread on this. Copied in the module and
adjusted the wordage and it works GREAT! Thank you very much for the
referral. Now, a question; I need to say LIKE rather than WHERE and can't
figure it out.

Here's Mr. Hookum's wordage that I used:

Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" & [FamID])

Mine says:

=Concatenate("SELECT Email FROM ConcatenateEmailsTable")

This works perfect for my project because I want all of the email
addresses.

HOWEVER, I want to save this and use it again and I just know I'll need to
say 'WHERE Email LIKE'. I can do the 'WHERE =' but the WHERE LIKE is
throwing me off. So, how would I say WHERE Email LIKE "*.net" in my
statement above.

I've got:

=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE [Email] LIKE
"
& "*.com")

And I get the error:

Run-time error '-2147217900 (80040e14)': Syntax error (missing operator)
in
query expression '[Email] LIKE *.com'.

So I know it's something simple like my quotes. Can you help? I'll wait
a
bit to see if you have the thread before posting this out.

Thanks very much for your advice.
--
Bonnie


[QUOTE="KARL DEWEY"]
Duane Hookom has a good solution --
Generic Concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
[/QUOTE][/QUOTE]

 

[Guest]

Hi Duane! Thank you SO much for your help. I pasted in your line and while
I no longer get any errors, the field is blank. I've checked and nearly half
of the email addresses are .com's so can't figure out what I'm doing wrong.
My field (e-mail address removed)

 

[Duane Hookom]

If you are using the ADO code, try change the "*" to "%".
=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE (e-mail address removed)

 

[Guest]

Duane,

Works PERFECTLY! Thanks.

--
Bonnie

If you are using the ADO code, try change the "*" to "%".
=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE (e-mail address removed)