My Access 2010 form returns no records if any of the boxes are left blank

[Becky De]

I've created an access 2010 form to run with a query that has the ability to search for data ranges in multiple fields. If I leave any of the boxes on the form blank, the query comes up with nothing.

I want to be able to search on all or some of the fields.

Here is the code. It only works if all the fields have values. Thanks for your help

WHERE ((([2009 Commercial Improved Database].Yr_blt)>=[Forms]![Commercial Improved Query Options].[Start Date] And ([2009 Commercial Improved Database].Yr_blt)<=[Forms]![Commercial Improved Query Options].[End Date]) AND (([2009 Commercial Improved Database].Sq_ft)>=[Forms]![Commercial Improved Query Options].[sqftlow] And ([2009 Commercial Improved Database].Sq_ft)<=[Forms]![Commercial Improved Query Options].[Sqfthigh]) AND (([2009 Commercial Improved Database].Market_val)>=[Forms]![Commercial Improved Query Options].[Value Low] And ([2009 Commercial Improved Database].Market_val)<=[Forms]![Commercial Improved Query Options].[Value high]) AND (([2009 Commercial Improved Database].Property_use_c d)=[Forms]![Commercial Improved Query Options].[PropCode1]))
ORDER BY [2009 Commercial Improved Database].Yr_blt, [2009 Commercial Improved Database].Sq_ft;

[colintis]

The problem in here is you use AND to link all the criteria, that's why all fields must not be empty.

To solve your problem, use OR instead to split the groups of the fields, something like this:

WHERE (<criteria> AND <criteria>) OR (<criteria> AND <criteria>) OR (.....and so on.

[Becky De]

Thank you, that almost fixes it but not quite.

I want to be able to search on some of the fields, but not necessarily all.

But when I change it to "OR", it doesn't narrow it to specific year and value, for instance. It brings up all of one year and all of the value, not the ones that correlate to the year and value together.

If I have "AND" in the sql, it doesn't pull any if one field in the form is left blank.

I want to narrow it down by all the fields that are filled out, but ignore any that are left blank in the form.

THANKS!

[colintis]

This does depends on how you nest the criteria with AND, OR, ( and ). If you still remember the maths taken in schools, there are similar formulas such as ((1+2)*4)/(3*4) = (((1+2)/3)*4. Put this method as how you place the criteria in your SQL, remember what's in common can be taken out as top level.

For checking all fields must not leaving blanks, you can group them as another criteria outside of the ones you need to filter the result.

SELECT xxx FROM xxx
WHERE (<Group of stuff to filter>)
AND (<Group of checking blank fields>)

[Rabbit]

Use IIf() to test for nulls and blanks and return the field if it is so.