Go Back   Access World Forums > Microsoft Access Reference > Access FAQs

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 11-13-2009, 03:12 PM   #1
ajetrumpet
Banned
 
Join Date: Jun 2007
Location: Universe - Local Group - Milky Way Galaxy - Orion Arm
Posts: 5,640
Thanks: 0
Thanked 97 Times in 44 Posts
ajetrumpet has a spectacular aura about ajetrumpet has a spectacular aura about
Send a message via MSN to ajetrumpet Send a message via Yahoo to ajetrumpet
Query by Combo Box / Query by Form Controls

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:
Quote:
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 Code:
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 Code:
WHERE (

orders.customer forms!frm!cbocustomer OR 

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

orders
.customer forms!frm!cbocustomer 
It's that simple. And there you have it folks! Glad I could help!
Attached Files
File Type: zip Query by Form.zip (24.1 KB, 1283 views)

ajetrumpet is offline  
The Following 2 Users Say Thank You to ajetrumpet For This Useful Post:
divanov (01-11-2015), hassanogaibi (06-28-2016)
Old 02-15-2010, 03:40 AM   #2
smithlanger
Registered User
 
Join Date: Feb 2010
Posts: 2
Thanks: 0
Thanked 1 Time in 1 Post
smithlanger is on a distinguished road
Re: Query by Combo Box / Query by Form Controls

Yes It is helpful coding. I make my problem solved.
smithlanger is offline  
The Following User Says Thank You to smithlanger For This Useful Post:
hassanogaibi (06-28-2016)
Old 08-30-2010, 03:15 AM   #3
mafhobb
Newly Registered User
 
Join Date: Feb 2006
Posts: 1,089
Thanks: 80
Thanked 2 Times in 2 Posts
mafhobb is on a distinguished road
Re: Query by Combo Box / Query by Form Controls

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

mafhobb

mafhobb is offline  
The Following User Says Thank You to mafhobb For This Useful Post:
hassanogaibi (06-28-2016)
Old 09-13-2010, 07:55 PM   #4
mcalex
Newly Registered User
 
Join Date: Jun 2009
Posts: 135
Thanks: 2
Thanked 10 Times in 8 Posts
mcalex is on a distinguished road
Re: Query by Combo Box / Query by Form Controls

@mafhobb

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

mcalex is offline  
The Following 2 Users Say Thank You to mcalex For This Useful Post:
divanov (01-11-2015), hassanogaibi (06-28-2016)
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Show filtered form records in a combo box Keith Nichols Forms 8 10-23-2006 03:55 PM
Combo Box versus List Box? Keith Nichols Forms 3 09-11-2006 11:48 AM
How to get value of combo box column in an unbound form field or query ChristineB Forms 2 10-10-2004 02:35 AM
Using Combo Box to Create Query Criteria mesci Forms 2 08-21-2002 04:26 AM
combo box on form pizaccess Forms 2 01-04-2002 03:48 PM




All times are GMT -8. The time now is 11:12 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World