Need to concatenate data from one field

B

Bonnie

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 [email protected]
Susan Gordan [email protected]
Sandy Smith [email protected]

My resulting data should be:

[email protected],[email protected],[email protected],

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

Thanks!
 
B

Bonnie

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

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

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]
 
B

Bonnie

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 is a text field.

I have: =Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE
[Email] LIKE '*.com' ")

I've also tried: =Concatenate("SELECT Email FROM ConcatenateEmailsTable
WHERE [Email] LIKE '*.com*' ")

No error, but no data.

???

Would appreciate any advice on what I'm doing wrong here.

Thanks very much for taking the time to help out folks like me!
--
Bonnie


[QUOTE="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 [Email] 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


:

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 [email protected]
Susan Gordan [email protected]
Sandy Smith [email protected]

My resulting data should be:

[email protected],[email protected],[email protected],

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

Thanks![/QUOTE][/QUOTE]
[/QUOTE]
 
D

Duane Hookom

If you are using the ADO code, try change the "*" to "%".
=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE LIKE
'%.com' ")

--
Duane Hookom
MS Access MVP


[QUOTE="Bonnie"]
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 [Email] is a text field.

I have: =Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE
[Email] LIKE '*.com' ")

I've also tried: =Concatenate("SELECT Email FROM ConcatenateEmailsTable
WHERE [Email] LIKE '*.com*' ")

No error, but no data.

???

Would appreciate any advice on what I'm doing wrong here.

Thanks very much for taking the time to help out folks like me!
--
Bonnie


[QUOTE="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 [Email] 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


:

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


:

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 [email protected]
Susan Gordan [email protected]
Sandy Smith [email protected]

My resulting data should be:

[email protected],[email protected],[email protected],

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

Thanks![/QUOTE]
[/QUOTE][/QUOTE]
 
B

Bonnie

Duane,

Works PERFECTLY! Thanks.

--
Bonnie


Duane Hookom said:
If you are using the ADO code, try change the "*" to "%".
=Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE LIKE
'%.com' ")

--
Duane Hookom
MS Access MVP


[QUOTE="Bonnie"]
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 [Email] is a text field.

I have: =Concatenate("SELECT Email FROM ConcatenateEmailsTable WHERE
[Email] LIKE '*.com' ")

I've also tried: =Concatenate("SELECT Email FROM ConcatenateEmailsTable
WHERE [Email] LIKE '*.com*' ")

No error, but no data.

???

Would appreciate any advice on what I'm doing wrong here.

Thanks very much for taking the time to help out folks like me!
--
Bonnie


[QUOTE="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 [Email] LIKE
'*.com' ")

--
Duane Hookom
MS Access MVP

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


:

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


:

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 [email protected]
Susan Gordan [email protected]
Sandy Smith [email protected]

My resulting data should be:

[email protected],[email protected],[email protected],

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

Thanks![/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
 
Top