Outputting query results into a single cell/text file 2

[postmanphat]

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

 

[lespaul]

try looking at FAQ701-4233

Leslie

Have you met Hardy Heron?

 

[postmanphat]

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

 

[postmanphat]

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!

 

[PHV]

You may consider the GetString method of the ADODB.Recordset object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

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):

SELECT [ClientName]
FROM tblClients
GROUP BY [Clientname]

Then you would create a query with the concatenate function:

SELECT [ClientName], Concatenate("SELECT [Email] FROM tblClients WHERE [ClientName] = """ & [ClientName] & """")

Duane
Hook'D on Access
MS Access MVP

 

[postmanphat]

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:

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.

'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

 

[dhookom]

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

 

[postmanphat]

dhookom - thats brilliant!!! If I could give you a thousand stars I would!!!