Ignoring Null values from Form in Query

[JPritch]

I have a form with 4 different search fields used to query a table and limit results: Policy#, SSN, Credit_Amt, Debit_Amt. Any matching rows based on the search criteria are returned in a subform.

My problem is how to handle any fields where the user doesn't specify a search value.

In my query, I have the Criteria set to:
Field: Policy_No
Criteria: =[Forms]![form name]![Policy#]

Field: SSN
Criteria: =[Forms]![form name]![SSN]

and so on....

If I place my criteria on different OR lines, the query runs, but if a user specifies more than 1 criteria, I get all the data for one criteria(such as policy# matches) and all the data for the second criteria(such as all the records for a certain credit_amt, regardless of Policy #).

If I place my criteria on the same criteria line, effectively making it AND, I get no results because I assume the database is looking for any blank search field values.

So, how do I make the query an "AND" query, but actually get results by ignoring any NULL search fields? So if a user enters a Policy# and Credit_Amt, the results will be only matching rows by Policy# AND Credit_Amt, and doesn't look at SSN and Debit_Amt.

I've tried different iterations using IIF(Not IsNull(.... to no avail.

Thanks!

 

[Alloyd]

Try something like this:

Like IIf([Forms]![form name]![Policy#] Is Null,"*",[Forms]![form name]![Policy#])

This should make it if nothing is selected, to return all values.

 

[JPritch]

Got it!

I tried searching here to no avail...but after writing my post, I thought of some new search terms and found a post by Pat Hartman:

http://www.access-programmers.co.uk/forums/showthread.php?t=95810&highlight=ignore+null

It's true: you can't do it in the regular query grid. I typed it all up in SQL view and returned to the query grid and there had to be 75 different criteria on different lines. It was ugly. But the SQL was clean, and the query ran and gave me my expected results!!!

 

[EMP]

It's true: you can't do it in the regular query grid. I typed it all up in SQL view and returned to the query grid and there had to be 75 different criteria on different lines. It was ugly.

See Jon's Basic Criteria Format in this thread:

Query By Form - using Text Boxes and Combo Boxes in criteria
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^