unable to use IIF statement results as critera in query

[delirium]

I have a field with the following IIF statement:

OWES: IIf([BALANCE]>0,"OWES",IIf([BALANCE]=0,"PAID",IIf([BALANCE]<0,"OVPD","")))

Simply returns OWES in the field if the balance is >$0, PAID if =$0, and OVPD if <$0. This works great ... the problem is that I want to use this query to power 2 different reports. One based on the records that have OWES in the column ... and another for OVPD.

When I put any value in the Criteria row ... the query comes back with no records. I have tried using the [Parameter Value] so I can enter the value I want on the fly, I have entered the value with and without quotes directly in the field, I have even gone so far as to eliminate all but the OWES value and filter by Is Not Null. No dice.


Oh ... and it's a make-table query used in a mail merge.

Thank you so much for your help.
Emily

 

[nharrison]

Is the field with the IIF statement located in a query or a table?

And you say that the field does store the value returned by the IIF statement? If this is the case then any criteria statement should function fine; the value returned by the IIF statement is a simple text value.

Finally, have you checked to make sure the query returns values without criteria? The query might be broken in a different way than you think. I know it seems like an obvious first step, just checking..

 

[SOS]

What I've found is if you have that as a field, create another query from this one and then the criteria should work on the field.

 

[nharrison]

Well typically when you have a calculated field, such as hers, the value is stored for use as soon as it is calculated, therefore it should be immediately accessible to use as a criteria.

for instance creating a field in a sales table
IIf(AnnualSales/12>1500, "Good Salesman", "Bad Salesman")

and then directly below in the criteria putting
"Good Saleman"

Obviously this example is a little redundant, but see my point?

 

[SOS]

Well typically when you have a calculated field, such as hers, the value is stored for use as soon as it is calculated, therefore it should be immediately accessible to use as a criteria.

I agree - SHOULD BE is the key. I have found that to NOT be the case in a lot of instances. So, creating another query from that query will, in 99% of the cases work whereas it doesn't seem to want to when you use it in the initial query. I've had to resort to that more times than I can count in order to make it work, where I didn't want to do that, but it worked where just putting the criteria in didn't.

 

[nharrison]

Very true. All programs have their quirks, Access included.

Emily, how is your field set up? At the moment is it stored in a separate query/table, which you then access in a different query? Or is the calculated field located in the same query in which you are trying to place a criterion?