Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access: Concatenate values from multiple records into one text string

Posted on 2011-03-15
5
Medium Priority
?
526 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:hfcit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 35140127
Access doesn't do concatenation of records natively so you'll probably have to use VBA. I wanted to do exactly that in one recent project and using a custom function was the way to go. Here's a page that gives a function to do this and explains exactly how to use it with all the caveats and requirements. It will allow you to specify your data set, a filter, a sort order, and a separator string, which in your case would be "; ".

http://allenbrowne.com/func-concat.html
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35140186
see this sample db

click the button Get Emails from the form
DB-Q-26888360-Concat.mdb
0
 

Author Comment

by:hfcit
ID: 35146728
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]","[QSubsTenderTradeEmailFilter]","[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?
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 2000 total points
ID: 35147817
The filter field should work like a SQL WHERE clause without the word WHERE. So for two conditions, you would separate them with AND, like this:

=ConcatRelated("[e-mail]","[QSubsTenderTradeEmailFilter]","[TenNo] = '" & Forms![Tender Analysis]!TenNo & "' AND [TradeCode] = '" & Forms![Tender Analysis]!Code & "'","","; ")

Note that I have replaced where you had literal double quotes with single quotes because SQL accepts single quotes around text items. I think it makes it easier to read with fewer quote marks in a row.
0
 

Author Comment

by:hfcit
ID: 35148705
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question