Nested IIF expression in my report's calculated control

[kdestef1]

I've added a checkbox for "Other" to a form where I would like for the formula to exclude/ignore specific text in my specimen type field. Only if the formula does not find the specific text from the formula, then I want it to check the box. In other words I don't want it to check the box if the specimen type = Blood, Stool, Urine, Like Fluid, Bone or CSF, but if the specimen type = Aspirate, for example, then I would want checkbox "Other" checked. My attempt at this formula is below. Using MS Access for MS 365. Thank you!



=IIf([Specimen Type]<>"Blood",0,IIf([Specimen Type]<>"Stool",0,IIf([Specimen Type]<>"Urine",0,IIf([Specimen Type]<>"Fluid",0,IIf([Specimen Type]<>"Bone",0,IIf(([Specimen Type]<>"CSF",0,[Specimen Type]))))))

[June7]

If expression is to set a checkbox, why would you have [Specimen Type] as a return? Should be -1 (or use True and False words)

So there are other values than "Aspirate" for which Other should be checked?

Nested IIf() is not appropriate. Need AND.

=IIf([Specimen Type]<>"Blood" AND [Specimen Type]<>"Stool" AND [Specimen Type]<>"Urine" AND [Specimen Type]<>"Fluid" AND [Specimen Type]<>"Bone" AND [Specimen Type]<>"CSF", True, False)

Probably should have a table of specimen types with a Yes/No field to identify as "Other". Include this field in combobx to select type and expression references that combobox column or include table in query and bind checkbox to field (locked so can't edit).

[kdestef1]

thanks June 7, it could return more than just Aspirate, as I mentioned, it was an example. I want a checked box for anything other than Blood, Stool, Urine, Fluid, Bone or CSF. Thanks, I think I have the gist of what needs correcting. Appreciate the quick response!

[kdestef1]

June 7 that worked, thanks again!