Display text in calculated query field based on multiple checkboxes

[timbits35]

I have 3 different yes/no checkboxes.

I would like to create a calculated field in my query that looks at all the checkboxes and displays text based on which checkboxes are selected.

Here are my fields
SpecialSS
SpecialDT
SpecialSGT

So if for example SpecialSS is checked off, I would like the calculated field to display "SS"
If SpecialSS and SpecialDT are both checked off, I would like it to display "SS, DT"

This works for 1 field
IIf([SpecialSS]=True,"(SS)","")

How would I incorporate the rest? I tried using or, but the result gave me -1 in the calculated field.

Thank you

 

[plog]

How married to your comma separator are you? Would "SS DT" be fine? Or do you need the comma between them when both true?

The easy way is no comma, you just string your Iif's together:

Trim(IIf([SpecialSS]=True,"SS ") & IIf([SpecialDT]=True,"DT ") & IIf([SpecialSGT]=True,"SGT"))

But if you need that comma this will need to be a user defined function in a module. The logic is a little more involved (nothing horrible, just more of it) which would require more space to write the code for that logic.

 

[mike60smart]

I have 3 different yes/no checkboxes.

I would like to create a calculated field in my query that looks at all the checkboxes and displays text based on which checkboxes are selected.

Here are my fields
SpecialSS
SpecialDT
SpecialSGT

So if for example SpecialSS is checked off, I would like the calculated field to display "SS"
If SpecialSS and SpecialDT are both checked off, I would like it to display "SS, DT"

This works for 1 field
IIf([SpecialSS]=True,"(SS)","")

How would I incorporate the rest? I tried using or, but the result gave me -1 in the calculated field.

Thank you

Hi
The following works but it is very rough.

 

[MarkK]

• Return the leading comma in all cases, and always remove it...

Mid(IIF(SpecialSS, ", SS", "") & IIF(SpecialDT, ", DT", "") & IIF(SpecialSGT, ", SGT", ""), 3)

• Also, in boolean math the operation '= True' is called the Identity Function and it has no effect on the outcome.
• False = True? False.
• True = True? True.