Passing criteria "NULL" or "IS NOT NULL" to a query

[SgtSaunders69]

I want to pass the criteria "NULL" or "IS NOT NULL" to the underlying query in a report from a field on a form where all criteria for the particular report is selected before running the report. Typicially the procedure I use is to place the name of the control from the form in the criteria field of the query... example... if I'm providing a date to the query, I put a date field on the form and put the full name of the field "forms!myform!fieldname" in the criteria field of the query underlying the report. This is pretty bullet proof, except when I want to pass a value of "null", or "not null" as the criteria for a given field in the query. How can I set up a combo box, or command button, or selection box to feed the values "NULL" or "NOT NULL" as criteria the underlying query?



Thanks all..

[June7]

If the combobox RowSource is a lookup table with no nulls, you can create a Null value in the RowSource with a UNION. I don't see any way to get the opposite.

SELECT fieldname FROM tablename UNION SELECT Null FROM tablename;

An alternative to the query parameter input is to construct the filter criteria in VBA code and use the WHERE clause of DoCmd.OpenReport to pass filter criteria to the report.