ajetrumpet
Banned
- Local time
- Yesterday, 19:15
- Joined
- Jun 22, 2007
- Messages
- 5,638
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:
For those of you looking at this file, you will notice the SQL for the subform is this:
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:
instead of this one:
It's that simple. And there you have it folks! Glad I could help! 
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:
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.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.
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);
PHP:
WHERE (
orders.customer = forms!frm!cbocustomer OR
forms!frm!cbocustomer IS NULL)
PHP:
WHERE
orders.customer = forms!frm!cbocustomer
