hfcit
asked on
Access: Concatenate values from multiple records into one text string
Hopefully this is a simple one, I've got a list of contacts email addresses in a report and I'm tring to join them into one text string located in a text box on the report to eventually copy paste into an email client, that would at least be a start anyway.
The query the report is based on lists [Name] [Email] [Address1] etc.
I've got no problem combining fields from the same record,
eg, [Name] & ", " & [Email]...
but how do I go about combining the values from multiple records into one string,
eg, [Email] & "; " & [Email] & "; " & [Email]...
I've seen a few concatenate solutions involving VBA code but I'm new to that so don't really know where to begin. Most other solutions are for slightly more complex situations than this so I'm wondering if there are any basics I've missed.
The query the report is based on lists [Name] [Email] [Address1] etc.
I've got no problem combining fields from the same record,
eg, [Name] & ", " & [Email]...
but how do I go about combining the values from multiple records into one string,
eg, [Email] & "; " & [Email] & "; " & [Email]...
I've seen a few concatenate solutions involving VBA code but I'm new to that so don't really know where to begin. Most other solutions are for slightly more complex situations than this so I'm wondering if there are any basics I've missed.
ASKER
Thanks for the replies,
I've tried both and am having more success with the module method from telyni19, I've got it to run almost perfectly but I'm trying to filter by 2 where conditions.
This is what I've got so far, which works...
=ConcatRelated("[e-mail]", "[QSubsTen derTradeEm ailFilter] ","[TenNo] = """ & Forms![Tender Analysis]!TenNo & """","","; ")
I need to add a second where condition of [TradeCode] = Forms![Tender Analysis]!Code but I'm not sure of the syntax. I've tried a few variations both inside and outside of the quote marks but I either get #Error or an unfiltered list of all the email addresses. Note, both [TenNo] and [TradeCode] are text fields.
Any ideas?
I've tried both and am having more success with the module method from telyni19, I've got it to run almost perfectly but I'm trying to filter by 2 where conditions.
This is what I've got so far, which works...
=ConcatRelated("[e-mail]",
I need to add a second where condition of [TradeCode] = Forms![Tender Analysis]!Code but I'm not sure of the syntax. I've tried a few variations both inside and outside of the quote marks but I either get #Error or an unfiltered list of all the email addresses. Note, both [TenNo] and [TradeCode] are text fields.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic! That works perfectly!
I shall go away now and learn all I can about SQL statements and VBA code. I'm fairly new to Access and haven't got into coding but it looks like it'll be useful now and in the future with the inevitable eventual transition to SQL server.
Thanks for your help.
I shall go away now and learn all I can about SQL statements and VBA code. I'm fairly new to Access and haven't got into coding but it looks like it'll be useful now and in the future with the inevitable eventual transition to SQL server.
Thanks for your help.
http://allenbrowne.com/func-concat.html