Using IN() with IIF in criteria


D

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")))
 
Ad

Advertisements

V

vanderghast

Have a computed expression:

iif( Forms]![Customizable On-Duty Personnel
Report]![ADay], fieldName IN("A", "B"),
fieldName IN("A", "B", "C"))


and its criteria
<> 0




Vanderghast, Access MVP
 
J

John Spencer

Or use this construction.

FieldName in ("A","B") Or
FieldName = IIF(Forms]![Customizable On-Duty Personnel Report]![ADay],"C","")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Have a computed expression:

iif( Forms]![Customizable On-Duty Personnel
Report]![ADay], fieldName IN("A", "B"),
fieldName IN("A", "B", "C"))


and its criteria
<> 0




Vanderghast, Access MVP


Dave Robinson said:
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")))
 
Ad

Advertisements

J

John W. Vinson

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")))

Repeating my answer from yesterday, which you may not have seen:

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"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top