Hello again all,

This question has again popped up, and is a very ancient issue among professional Access developers, so here is the solution thread for newbies! Here is the question that is on everyone's mind:I have a query that gets criteria from 3 different combo boxes. What I want to have happen is if I leave one combo box blank, the query simply ignores that combo box value. Also, if I leave all combo boxes blank, I want the query to return all of the records.Well guess what!? Now you don't have to worry about how to do this. The attachment here says it all. When you open it, you will see that there are 3 combos on the form, and the subform IS a query that is based off of those combos. Running an actual query object does the exact same thing as what you see in the attachment, so the concept is the same.


For those of you looking at this file, you will notice the SQL for the subform is this:SELECT orders.*

FROM orders

WHERE (

orders.customer = forms!frm!cbocustomer OR

forms!frm!cbocustomer IS NULL) AND

(orders.item = forms!frm!cboitem OR

forms!frm!cboitem IS NULL) AND

(orders.carrier = forms!frm!cbocarrier OR

forms!frm!cbocarrier IS NULL);The magic to this entire concept are the "IS NULL" portions of the query you see above. In basic terms, if you want to ask Access to "ignore the combo box if I leave it blank", then simply write this SQL:WHERE (

orders.customer = forms!frm!cbocustomer OR

forms!frm!cbocustomer IS NULL)instead of this one:WHERE

orders.customer = forms!frm!cbocustomerIt's that simple. And there you have it folks! Glad I could help! :)

Yes It is helpful coding. I make my problem solved.

Yes, but how do you do it if you were doing in in design mode instead of SQL?

mafhobb

@mafhobb

In the Criteria cell of the query window, add "OR IS NULL" without quotes, after whatever other condition you are querying for.