Using IN() with IIF in criteria

[Dave Robinson]

I have a form with a checkbox for "A and B". The letters A and B are
possible values (the other possibilities are C and E) in a field of a table
upon which a query is based. I am trying to figure out how to write an iif
statement that will set the criteria for the field as IN("A","B") if that
checkbox is checked and IN("A","B","C") if it isn't checked.

I tried the following but I get a syntax error:

In (IIf([Forms]![Customizable On-Duty Personnel
Report]![ADay]=True,("A","B"),("A","B","C")))

Thanks for any help.

 

[Dorian]

What do you mean by "set the criteria for the field"?
Is this in a query? Or in VB code? Or what?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John W. Vinson]

On Tue, 18 Aug 2009 13:07:01 -0700, Dave Robinson <Dave

I have a form with a checkbox for "A and B". The letters A and B are
possible values (the other possibilities are C and E) in a field of a table
upon which a query is based. I am trying to figure out how to write an iif
statement that will set the criteria for the field as IN("A","B") if that
checkbox is checked and IN("A","B","C") if it isn't checked.

I tried the following but I get a syntax error:

In (IIf([Forms]![Customizable On-Duty Personnel
Report]![ADay]=True,("A","B"),("A","B","C")))

Thanks for any help.

You can't pass operators or delimiters or commas as a parameter - only actual
values. Try using Boolean logic:

WHERE ([Forms]![Customizable On-Duty Personnel Report]![ADay] = True AND
fieldname IN ("A", "B")) OR ([Forms]![Customizable On-Duty Personnel
Report]![ADay] = False AND fieldname IN ("A", "B", "C"))