Concatenate text field in a report

[Jen]

Hi~
I'm trying to create a report, and having some trouble. I have a
table with fields of the following:

Number
Description
Value

and in my report, I would like to have the records with the same
number to be grouped. I

I have it working so that it shows the number once and a total, but
then it shows a line for each description. That is a text field, so
the only option for totals is to count the number. I would like to
concatenate those fields and separate with a comma.

Right now I get:

Number Total
Description 1
Description 2
Description 3

Number2 Total2
Description 1
Description 2
Description 3

Number Description 1, Description 2, Description 3 Total
Number2 Description 1, Description 2, Description 3 Total2

Thanks in advance for your help!!

 

[John Spencer]

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Make sure you make the necessary code changes to the function if you are
using DAO (.mdb database) and save the function in a module. The module
cannot have the same name as the function.
--
Duane Hookom
MS Access MVP

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jen]

Duane ~
Thank you so much! It looks like exactly what I need. I have never
added a module to my access database. Is there anything I need to do
besides add the module and then use it in my query? I am using Access
2007 and getting the following error,
Undefined Function 'Concatenate' in expression.

When I first ran the module it highlighted "New ADODB.Recordset" with
an error.

Thanks in advance for your help!
Jen

 

[Jen]

I found where to select the ADO in References, but now I'm still
getting an error. I believe it is because my tables and fields have
spaces, so I'm not sure how to get that to work!

Here is my function call:

Concatenate("SELECT [Donated Item] & ' (' & [Business Donating Item] &
')' FROM [Donation Table] WHERE [Auction Number] =" & [Auction
Number]) AS DonationList

Thank you!!
Jen

 

[Jen]

I found where to select the ADO in References, but now I'm still
getting an error. �I believe it is because my tables and fields have
spaces, so I'm not sure how to get that to work!

Here is my function call:

Concatenate("SELECT [Donated Item] & ' (' & [Business Donating Item] &
')' FROM [Donation Table] WHERE [Auction Number] =" & [Auction
Number]) AS DonationList

Thank you!!
Jen

I got it! I had Null values in my Auction Number column. Thank you
so much! You made my life so much easier with this module!
Jen