IIF statement problem in Query Criteria

[Guest]

I have a field "SumOfQty" in a query that I want to use an IIF statment in
the criteria: If [Forms]![Zero Qty]![Checkbox] is checked then "SumOfQty"
criteria is 0.
else >0. The 0 works, but it won't see the >0. What would be the solution
to get the same results another way?

 

[Guest]

Sally -

Try something like IIF([Forms]![Zero Qty]![Checkbox] = -1, 0, [fieldname])

where fieldname is the name of the field whose data you want to insert. If
no fieldname is applicable, try entering some number instead of fieldname,
like
IIF([Forms]![Zero Qty]![Checkbox] = -1, 0, 3) (for example).

If this isn't what you need, would you post your IIF expression.

 

[John Spencer]

Add SumOfQtry to the query grid TWO TIMES and then set the criteria as
follows

Field: SumOfQty
Criteria(1): [Forms]![Zero Qty]![Checkbox]=True
Criteria(2): [Forms]![Zero Qty]![Checkbox]=False

Field: SumOfQty
Criteria(1): 0
Criteria(2): >0

You can't use an IIF statement in a query to set the comparison operator (=,
<, >, etc.), you can only use the IIF statement to decide the values to be
compared.

By the way, when you save the query, Access will rearrange things by moving
[Forms]![Zero Qty]![Checkbox] into a Field "Cell". You can accomplish the
same thing by doing

Field: [Forms]![Zero Qty]![Checkbox]
Criteria(1): True
Criteria(2): False

Field: SumOfQty
Criteria(1): 0
Criteria(2): >0

I posted the first solution based on a guess that you might have more
criteria than just the one you posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

John: That worked perfect! Boy has that opened up by mind for other
possiblities! Thanks again for your expert help!

Add SumOfQtry to the query grid TWO TIMES and then set the criteria as
follows

Field: SumOfQty
Criteria(1): [Forms]![Zero Qty]![Checkbox]=True
Criteria(2): [Forms]![Zero Qty]![Checkbox]=False

Field: SumOfQty
Criteria(1): 0
Criteria(2): >0

You can't use an IIF statement in a query to set the comparison operator (=,
<, >, etc.), you can only use the IIF statement to decide the values to be
compared.

By the way, when you save the query, Access will rearrange things by moving
[Forms]![Zero Qty]![Checkbox] into a Field "Cell". You can accomplish the
same thing by doing

Field: [Forms]![Zero Qty]![Checkbox]
Criteria(1): True
Criteria(2): False

Field: SumOfQty
Criteria(1): 0
Criteria(2): >0

I posted the first solution based on a guess that you might have more
criteria than just the one you posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a field "SumOfQty" in a query that I want to use an IIF statment in
the criteria: If [Forms]![Zero Qty]![Checkbox] is checked then "SumOfQty"
criteria is 0.
else >0. The 0 works, but it won't see the >0. What would be the
solution
to get the same results another way?