Conditionally Combining Detail Records

[JB]

I have a report that prints out TV shows and their standard airtimes.
Most shows have only one airtime and print out fine. If a show has more
than one airtime, it prints out on many lines. My client wants one line
per show regardless of airtimes. If there are two duplicate shows, I am
to print both airtimes on the same line. If there are three or more
airtimes, I am to print the word "Various". I am not sure of the most
efficient way to do this.

My query for this report is already quite complex, it does other things
besides what I have mentioned, with nested queries and 12 tables
joined. I have not been able to modify the query to create the desired
effect. I am not sure if I can use a function called from the report or
if I need to use a function to read the recordsource and write new
modified records to a separate table and run the report off the newly
created table.

This is an example of what happens now:

Program Period
Show 1 Mon 8:00 pm
Show 2 Tue 8:00 pm
Show 2 Wed 8:00 pm
Show 3 Wed 9:00 pm
Show 3 Wed 9:30 pm
Show 3 Fri 9:00 pm

This is an example of what the client wants:

Program Period
Show 1 Mon 8:00 pm
Show 2 Tue 8:00 pm / Wed 8:00 pm
Show 3 Various

I am somewhat capable with writing code but am certainly no expert. I
have seen reference to a Concatenate function and some others but am
not sure if they apply to my situation or how to modify them for my
scenario. Any advice is greatly appreciated.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[JB]

Thanks Duane.

I had to make a lot of modifications to the function and to my query
but this was a great boost to help me along. The best part is that I
did not have to touch my report. I was able to do everything in the
query and function.

You have helped me in the past with this project and the complex issues
that the client needs from the reports. I have learned some great
tricks that I never knew were possible. I appreciate your contributions
to the forum and individuals like me.

 

[Duane Hookom]

Glad to hear that my stuff and experience have some value.