Using Form Control value to determine query criteria

[jobrien4]

I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.

I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).

I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.

Things I have tried to no avail:
Putting a Iif statement in the query criteria: gives an error that criteria is too complex
Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work
Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run

Any ideas?

 

[jobrien4]

So I figured out that my Iif was missing a Like in the beginning. I'm now able to see the Ready Only records when the checkbox is clicked. Now my problem is the query doesn't show all records (including not ready) if the checkbox is unchecked. It's not picking up the "*" when ReadyOnly <> 0

Any ideas:

Like IIf([Forms]![Secondary Process Lineup]![ReadyOnly]=0,(Sum([informix_intprd_rec].[prd_ohd_pcs]))>0,"*")

 

[TJPoorman]

Is this set up as a field? or as criteria?

If it's as criteria try removing the like statement and on the values if true or false change to this:

IIf([Forms]![Secondary Process Lineup]![ReadyOnly]=0,>0)