Seperate query results with a comma

[Michael]

I have a report that requires a summary of the country of manufacture of the
items contained therein. Sometimes I have only 1 country and it works fine.
When I have multiple countries, I need to take the results and put them in 1
field so it appears on the report like: "UK, USA, Russia, Sweden". Since the
number of countries will vary, I need to know how to read the number of
records and display the results in 1 field. Any ideas?

 

[Allen Browne]

You need a VBA function to concatenate the values together.

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

 

[fredg]

I have a report that requires a summary of the country of manufacture of the
items contained therein. Sometimes I have only 1 country and it works fine.
When I have multiple countries, I need to take the results and put them in 1
field so it appears on the report like: "UK, USA, Russia, Sweden". Since the
number of countries will vary, I need to know how to read the number of
records and display the results in 1 field. Any ideas?

The following will work.
Place [ControlName] in the Report Footer.
Set it's CanGrow property to Yes.
Also set the Report Footer CanGrow to Yes.

Also in the Report Footer, add an unbound text control.
Set it's control source to:
=Count("*")
Name this control "RecCount"
You can make this control not visible.

Code the Format Event of the section of the report that shows the
[CountryName] field, i.e. Detail Format event:

If InStr(Nz([ControlName], ""), [CountryName]) = 0 Then
[ControlName] = [ControlName] & [CountryName] & ", "
End If
===============
Then in the Report Footer Print event:
[ControlName] = "There were " & [RecCount] & " records."& vbNewLine &
[ControlName]

The control will show the total number of records, with just one
listing of a country.

Change the [CountryName] field name to whatever your actual field name
is.

The print out will look like this:
There were 25 records.
Brazil, France, Sweden, etc.,

 

[Michael]

That worked great. Thanks a lot. It was just what I needed.