Help with IIF ans IsNull!

J

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.
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top