Conditional Criteria Conundrum

[nypedestrian]

I created a query for an Order Shipping Status report. I created a dialog form to pass the value on the form to the criteria portion of the query. That worked out fine but I need to pass another value to a different field criteria and I cannot figure out how to do it. I've tried different things but it's not working.



Basically, I have a yes/no checkbox to show orders with balanced owed. I want to pass the value of >0 (Yes) or 0 (No) to the [OrderBalance] criteria on the query.

I tried to do an IIF statement (in the criteria portion): IIF([Forms]![dfrmOrderStatus]![Balance]=Yes,>0,0).

Also did variations of this statement (including taking the " " from the Yes and the >0 and 0):

IIF([Forms]![dfrmOrderStatus]![Balance]="Yes",">0","0")

IIF([Forms]![dfrmOrderStatus]![Balance]="Yes",[qryOrderCalcBalance].[OrderBalance]>0,0)

IIF([Forms]![dfrmOrderStatus]![Balance]="Yes",[qryOrderCalcBalance].[OrderBalance]>0,[qryOrderCalcBalance].[OrderBalance]=0)

Am I missing something or is this something that cannot be done in the criteria portion of a query? Not really well versed in vba so I wanted to avoid that route. Thank you.

[rpeare]

a yes value on a checkbox is actually stored as a -1, not a 1 or a "YES"

You have a few options

1. You can build a SQL statement on the fly and change the record source of your report when you click your button

2. You can create a separate query for each possibility and either create a report for each possible query or change the record source

3. Create an 'indicator' field in your query that changes based on your choices.

The easiest thing to do is #3 if you're not great with code.

For example if you create a field in your query like this:

Show: IIf(IsNull([OrderBalance]) or [orderbalance] = 0,(IIf([forms]![dfrmOrderStatus]![orders]=-1,1,0)),1)

Then add a criteria of 1 to the SHOW field.

In my example I created a table with a bunch of 0 and or null fields on a numeric field as well as some record that had data. With this formula I could exclude or include anyone who had a zero or null balance by using a 1 in the criteria.

[nypedestrian]

a yes value on a checkbox is actually stored as a -1, not a 1 or a "YES"

You have a few options

1. You can build a SQL statement on the fly and change the record source of your report when you click your button

2. You can create a separate query for each possibility and either create a report for each possible query or change the record source

3. Create an 'indicator' field in your query that changes based on your choices.

The easiest thing to do is #3 if you're not great with code.

For example if you create a field in your query like this:

Show: IIf(IsNull([OrderBalance]) or [orderbalance] = 0,(IIf([forms]![dfrmOrderStatus]![orders]=-1,1,0)),1)

Then add a criteria of 1 to the SHOW field.

In my example I created a table with a bunch of 0 and or null fields on a numeric field as well as some record that had data. With this formula I could exclude or include anyone who had a zero or null balance by using a 1 in the criteria.

Thank you for telling me about the value of the Yes in a checkbox, I was trying to find it but I guess I wasn't looking hard enough.