Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Outputting query results into a single cell/text file 2

Status
Not open for further replies.

postmanphat

Technical User
Nov 13, 2006
117
GB
Hi,

I have a table that contains email addresses within client records. I need to be able to extract from the database a string that I can just copy into my 'To' bar in Outlook 07.

So for example, say I have the following addresses in my db:

someone@somewhere.com
dog@cat.com
cat@dog.com

I need to output a string that looks like this:

"someone@somewhere.com; dog@cat.com; cat@dog.com"

I can then simply copy this line into outlook to do a mailout.

Any ideas would as ever be most appreciated.

Thanks in advance

Dave
 
Hi

Thanks for that, but I'm struggling to make it work. The article above describes how to do it if your data is spread over 2 tables which mine isn't.

I simply have a column called 'email' in my tblClients table. I just want to output all of the entries in this column into a single cell seperated by a ';'.

Any further suggestions would as ever be gratefully received.

Thanks

Dave
 
Also, Access doesn't seem to recognise the concatenate function using the above example, although I imagine this is more to do with my syntax!
 
You may consider the GetString method of the ADODB.Recordset object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
postmanphat,
You must copy the function into a new module and give the module a name that isn't the same as the function. Maybe name the module "modStringFunctions".

You don't need two tables. You could first create a query that groups by the appropriate repeating columns from your table. Assuming a table structure of:
[tt][blue]
tblClients
==========================
ClientName (repeating field)
Email (multiple values per ClientName)
[/blue][/tt]
You would create a group by query (qgrpClients):
Code:
SELECT [ClientName]
FROM tblClients
GROUP BY [Clientname]
Then you would create a query with the concatenate function:
Code:
SELECT [ClientName], Concatenate("SELECT [Email] FROM tblClients WHERE [ClientName] = """ & [ClientName] & """")


Duane
Hook'D on Access
MS Access MVP
 
Hi,

I still get the message "Undefined function 'concatenate' in expression". I don't quite understand why you guys are all picking up on grouping it by surname.

My database is just a simple repository of client details. What I am trying to do is to create a string of all the email addresses in my db for sending out a generic newsletter to.

What I have is a client table like so:
Code:
ClientID   Forename   Surname     Email
-------------------------------------------
1          Dave       Sturridge   dave@somewhere.com
2          Elvis      Presley     elvis@graceland.com
3          Ivor       Bigun       ivor@wherever.com
...
I want to be able to run a query that returns the following result in a single cell from the above dataset.

Code:
'dave@somewhere.com, elvis@graceland.com, ivor@wherever.com'

I can then paste this row of email addresses into my 'To' field in Outlook and send out my newsletter to all the people in my database.

Thanks again all for all the assistance!

Dave

 
Please reply to my statement "You must copy the function into a new module and give the module a name that isn't the same as the function. Maybe name the module modStringFunctions." Did you do this?

If you are only interested in getting the emails you can get them into a text box on a form. Create a form with a single, large text box. Set the control source to:
[tt][blue]
=Concatenate("SELECT EMAIL FROM tblClients")
[/blue][/tt]
Since most email programs prefer semi-colons rather than commas, you might use:
[tt][blue]
=Concatenate("SELECT EMAIL FROM tblClients",";")
[/blue][/tt]



Duane
Hook'D on Access
MS Access MVP
 
dhookom - thats brilliant!!! If I could give you a thousand stars I would!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top