Concatenate data from multiple lines in a recordsource

[Lesli]

I hope I can explain this so that it makes sense...

Data basics:
I have a form with providers (using tblProviders) and a subform with
counties (using tlkpCounties) they serve. So, a one to many relationship
between the providers and the counties.

When I create the recordsource for my report I end up with multiple lines
per provider.

Prov1, Adams
Prov1, Athens
Prov1, Franklin
Prov2, Cuyahoga
Prov2, Lake
and so on...

On the report, I would like to be able to have a single block of
concatenated text (sorted alphabetically) that shows the counties they serve.
Like this:

Prov1: Adams, Athens, Franklin
Prov2: Cuyahoga, Lake

I can get rid of the multiple detail lines for the provider name, etc. by
creating group headers for the provider. What I can't figure out how to do
is create the concatenated text block with the county names and have it show
up correctly on the report. I can concatenate the counties using a loop, but
the same list shows up for every provider. How do I get it to show up for
only the provider that it is applicable to.

I used the form/subform method to store the counties served because I
couldn't figure out a better way to do it. I thought about using a
multi-select listbox but don't know enough about them to figure out how to
store and retrieve the data from one.

Any solutions or altenative uggestions would be very helpful.

Thanks and Happy Thanksgiving!
Lesli

 

[Ken Snell \(MVP\)]

Check out Duane Hookom's generic "concatenate" function. You can use it to
do what you ask here.

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

 

[Lesli]

It worked! Thank you for the link. I had seen a reference to this in
another thread, but that link didn't work.

Thanks,
Lesli