Include or exclude records with a Zero value field using a checkbox on a form. (1 Viewer)

[klsblues]

I have a query that lists products and prices and a form that I use to print Various product price lists.

On a form, I select a product group and I can print off the current price list.

For certain purposes I want to include zero value sell price (it highlights to me that i need to add the price), but for the department list, I don't want to include the zero value records.

I have a form with a checkbox called "IncZeros" and in the query criteria i have tried the following combinations....

IIf([Forms]![frmMenuLabels]![IncZeros]=True,[ProductsSell],[ProductsSell]<>0)
IIf([Forms]![frmMenuLabels]![IncZeros]=-1,[ProductsSell],[ProductsSell]<>0)
IIf([Forms]![frmMenuLabels]![IncZeros]=True,[ProductsSell],[ProductsSell]>0)
IIf([Forms]![frmMenuLabels]![IncZeros]=-1,[ProductsSell],[ProductsSell]>0)

If I select the checkbox [IncZeros] (True) all my records appear in the query which is great, however if I de-select the checkbox (False), it only shows records with zero values in the [SellPrice} field and no records with a price in the [SellPrice] field.

I know I could create separate queries and reports, but there are 7 queries and 12 reports and I don't particularly want to re-create them.

Any ideas would be gratefully recieved.

 

[Gasman]

I have never been much of a fan of tying a query to a form control. Much prefer to set a TempVar in any form and use that in the query as criteria.
I would amend the record source of the form in VBA in this case.

If IncZeros is True, no criteria required. If False add "ProductsSell >0"
If you have Nulls, use the NZ() function.