IIF statement problem in Query Criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sally - 30 Aug 2007 22:38 GMT

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?

Reply to this Message

kasab - 31 Aug 2007 03:28 GMT

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.

> 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?

Reply to this Message

John Spencer - 31 Aug 2007 12:41 GMT

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.

Signature

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?

Reply to this Message

Sally - 31 Aug 2007 21:02 GMT

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
[quoted text clipped - 32 lines]
> > solution
> > to get the same results another way?

Reply to this Message