Query by Combo Box / Query by Form Controls (1 Viewer)

Status
Not open for further replies.

ajetrumpet

Banned
Local time
Today, 16:59
Joined
Jun 22, 2007
Messages
5,640
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:
PHP:
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:
PHP:
WHERE (

orders.customer = forms!frm!cbocustomer OR 

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

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

Attachments

mafhobb

Registered User.
Local time
Today, 16:59
Joined
Feb 28, 2006
Messages
1,092
Yes, but how do you do it if you were doing in in design mode instead of SQL?

mafhobb
 

mcalex

Registered User.
Local time
Tomorrow, 05:59
Joined
Jun 18, 2009
Messages
135
@mafhobb

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

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom