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
..
Sally said:
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?