compiling several fields (Access 97)

[Fay Yocum]

I have a table with 26 yes/no fields for periods of equipment maintenance. The user will be able to check multiple periods. What the user wants is a list of all periods that maintenance is do. For example a piece of equipment is marked for maintenance due in periods 4, 8,12, 16, 20, and 24. The report should show 4, 8,12, 16, 20, 24. What is the best approach to this result?

Thank you.
Fay

[HansV]

Having 26 yes/no field is not an ideal design, but it might not be easy to change it.

Let's say that these fields are named M1, M2, ..., M26 (I'm lazy). You can use the following expression in a query:

Ms: IIf([M1], "1, ", "") & IIf([M2], "2, ", "") & ... & IIf([M26], "26, ", "")

You can't afford to be lazy and will have to write out M3, M4 etc. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

Instead, you can calculate the concatenation directly in the control source of a text box on the report:

=IIf([M1], "1, ", "") & IIf([M2], "2, ", "") & ... & IIf([M26], "26, ", "")

In both cases, the string will end in a comma and a space. If this bothers you, add another field too the query (it becomes too complicated to do this directly):

MaintenancePeriods: IIf([Ms] = "", "", Left([Ms], Len([Ms])-2))

[Fay Yocum]

Thanks Hans. I got it to work this morning. I couldn't get it to work last night. But it worked great this mornig. Fay

PS cute evil grin!