Concatenate Field Contents

Question

Hello,

I am running MS Access 2010 on a Win7 machine. Below is a screenshot of my query named selVAS_CodesSelected. For my report, I need to concatenate the contents of the Description field with a semicolon between each record. it should look like this:

Golf Tournament Box Required; Brown Gift Box required; Blue Gift Box required; Letter / Notecard to be inserted

How can this be done?

Thank you, Kevin

Answer 1

You can use a function such as: http://allenbrowne.com/func-concat.html , but you need a common field to based the concatenation on.  There needs to be something that links all the records to base the grouping on.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Here's another link with a demo http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16.html

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 3

Hi Kevin,

Just wanted to offer one more link for your consideration.

SimpleCSV()

For your report, you can place an unbound Textbox with the following Control Source:

=SimpleCSV("SELECT Description FROM selVAS_CodesSelected", ";")

Hope it helps...

Answer 4

Hi DBguy,

Thanks for the suggestion. I placed the VB in a module and created an unbound textbox on my report and inserted =SimpleCSV("SELECT Description FROM selVAS_CodesSelected", ";") in the Control Source. When I click off the Control Source property in my report, I get the following error:



What could cause this error?

Thanks, Kevin

Answer 5

Hi Kevin,

Did you make sure you did not name the module the same as SimpleCSV?

If the name of the module is different than the function, then double-check the name of the field and the query.

If everything is correct, try stepping through the code using the Immediate Window by entering something like:

?SiimpleCSV("SELECT Description FROM selVAS_CodesSelected", ";")

Just my 2 cents...

Answer 6

Hi DBguy,

I'm using your suggestion in a query and it worked great. I'm now able to perform a DLookUp for my report.

Thanks for your help.

Cheers

Answer 7

Hi Kevin,

You're welcome. Daniel and I were happy to assist. Good luck with your project.