Help with IIF ans IsNull!

[JBurlison]

Basically i have a drop down that i want controlling the Criteria for each
field in my query to have an advanced search. If i leave a field blank the
query comes up with nothing so i tried this:


Code:

IIf(IsNull([Forms]![Inventory Report Search]![Model]),Is
Null,[Forms]![Inventory Report Search]![Model])

this is not working. how do i make is so if a field is blank it will return
it as null or not even there.

Note: also tried this;

Code:

Forms]![Inventory Report Search]![Model] Or Forms]![Inventory Report
Search]![Model] Is Null

works, But comes back as too complex after a few searches and when i open
the query there is a million or's in there. so that wont work.

 

[Allen Browne]

Switch the query to SQL View (View menu), and manipulate the WHERE clause
manually.

If the criteria is under a field named Field1, locate where the query says:
WHERE (Field1 = ...
and enter something like this:
WHERE (([Forms]![Inventory Report Search]![Model] Is Null
OR (Field1 = [Forms]![Inventory Report Search]![Model]))

A better solution (particularly if you do have 10^6 records), would be to
build the filter string from only those boxes where the user actually
entered something. See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example, and pull it apart to see how to build the filter
string for different data types and even for ranges of data.

The article also gives an explanation of the solution suggested above.