Combining Checkboxes into one field if true

[Brenda Washington]

I have 9 checkboxes on a form. The user could potentially
have from 0 - 9 checked. I want to create a query where
it combines all checkboxes that are checked into one
field. (for ease of reporting)


Example:

tblInvoiceType has InvoiceTypeID, ProjectID, Type1, Type2,
Type3, ...

If a row has Type1 and Type3 checked, I want the query to
show ProjectID, NameofType1 & ", " & NameofType3

Thank you in advance for any help/hints you can give!

 

[John Spencer (MVP)]

Well, you could do it in a query with a long IIF statement. See Below:


SELECT InvoiceTypeID, ProjectID,
IFF(Type1 or Type2 or Type3 or ... Type9,
LEFT(IIF(Type1,"Type1, ","") &
IIF(Type2,"Type2, ","") &
...
IIF(Type9,"Type9, ",""),
Len(IIF(Type1,"Type1, ","") &
IIF(Type2,"Type2, ","") &
...
IIF(Type9,"Type9, ","") -1), "") as TypesChecked
FROM tblInvoiceType

Or better you could just write a user function in VBA to do this.

OR even better, you could normalize your table structure.

 

[Brenda Washington]

Thanks for your reply... I currently have a normalized
table structure, but was trying to play around with
breaking normalization to make the invoice types work.

Tfor getting my mind to think of other options.

-Brenda