Ignoring Null Selections in Query Generator

[ryan.crowe]

I am creating a query/report generator module for my database that I
hope will allow a user to dynamically create a report based on any
combination of data that has been entered. The query's criteria is
collected in a number of combo and text boxes on a single form. The
problem is when the query is run, I can figure out how to get the query
to ignore the null or blank fields on the form. I have tried adding
"Like [Forms]![FormName]![ControlName].[value] Is Not Null Or ="""
as the 'Or' criteria in design of the query but this does not seem
to do anything.

There are too many different scenarios to build a query for each
possibility and I don't really know how to write a query that
conditionally adds "Where" clauses based on what is not null.

What am I missing here?

 

[Allen Browne]

Download this little example database that works with Access 2000 and later:
http://allenbrowne.com/unlinked/Search2000.zip

The example illustrates how to build the filter string for a form, based on
any combination of different types of field. The code to build the
WhereCondition for a report is identical, but instead of ending with:
Me.Filter = strWhere
you use:
DoCmd.OpenReport "Report1", acViewPreview, , strWhere

It is actually possible to build the WHERE clause of a query so it returns
True if the form contains a Null, e.g.:
WHERE (([Forms]![FormName]![ControlName] Is Null)
OR ([Field1] = [Forms]![FormName]![ControlName]))
AND ...
But that is very clumsy to build, and inefficient to execute.