Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 10-24-2013, 11:09 AM   #1
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Ignoring null criteria in a multi-criteria query…

Hello, I am working on a query that uses 3 combo boxes in a form to use as criteria in a query. As it is private data let’s say the combo boxes are “Model”, “color” and “size”. So far I have been about to query data if all three combo boxes are filled out correctly and matches a record. However I would like it to be able to show matching records if only 1 or 2 of the boxes are filled.

For example if the user selects red as the color I would like a query to show all of the records that are red regardless of the model or size. Or if they select Model Z I want the query to show all the colors and sizes for Model Z.

How would I do this?

Nano is offline   Reply With Quote
Old 10-24-2013, 11:51 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,938
Thanks: 13
Thanked 4,051 Times in 3,987 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Ignoring null criteria in a multi-criteria query…

One way:

http://access.mvps.org/access/queries/qry0001.htm
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Nano (10-24-2013)
Old 10-24-2013, 12:08 PM   #3
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

Thanks works great!

Nano is offline   Reply With Quote
Old 10-24-2013, 12:11 PM   #4
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

Well maybe not, now it brings back all of the records even if it doesn't meet the other criteria
Nano is offline   Reply With Quote
Old 10-24-2013, 02:35 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,938
Thanks: 13
Thanked 4,051 Times in 3,987 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Ignoring null criteria in a multi-criteria query…

The "And's" and "or's" and parentheses might have gotten screwed up. Access will do crazy things to it in design view, but in SQL view you'd want this type of thing:

WHERE (Field1 = Combo1 OR Combo1 Is Null) AND (Field2 = Combo2 OR Combo2 Is Null)
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-25-2013, 06:47 AM   #6
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

Ok here is the code I pasted in in the SQL view to replace the old "Where" however now it is prompting me for it type of data rather then looking at the form. Do you see a problem with my code?



WHERE ([Forms]![Imput CSG]![ModelCB] = ModelCB OR ModelCB Is Null) AND ([Forms]![Imput CSG]![colorCB] = colorCB OR colorCB Is Null) AND ([Forms]![Imput CSG]![sizeCB] = sizeCB OR sizeCB Is Null)
Nano is offline   Reply With Quote
Old 10-25-2013, 07:04 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,938
Thanks: 13
Thanked 4,051 Times in 3,987 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Ignoring null criteria in a multi-criteria query…

Is the form open when the query runs? It needs to be. If it is, something must be misspelled.

Also, the second part of each should refer to the combo, not the field:

WHERE ([Forms]![Imput CSG]![ModelCB] = ModelCB OR [Forms]![Imput CSG]![ModelCB] Is Null) AND ([Forms]![Imput CSG]![colorCB] = colorCB OR [Forms]![Imput CSG]![colorCB] Is Null) AND ([Forms]![Imput CSG]![sizeCB] = sizeCB OR [Forms]![Imput CSG]![sizeCB] Is Null)

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-25-2013, 07:54 AM   #8
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

Yes the form is open when running this query.
Ok, I will fix that part, that is likely my problem.
Nano is offline   Reply With Quote
Old 10-25-2013, 08:01 AM   #9
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

No sadly it is still not working. I might have to try something else like an iif statement.
Nano is offline   Reply With Quote
Old 10-25-2013, 08:03 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,938
Thanks: 13
Thanked 4,051 Times in 3,987 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Ignoring null criteria in a multi-criteria query…

Can you post the db here?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Nano (10-25-2013)
Old 10-25-2013, 08:10 AM   #11
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

No I can't, it is for my job and has too much private information to wipe. I am sure I can figure it out with iif statements eventually. Although it would be a whole lot simpler this way.

Thank you for trying.
Nano is offline   Reply With Quote
Old 10-25-2013, 11:28 PM   #12
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Ignoring null criteria in a multi-criteria query…

What Paul has suggested is the bog standard approach so should work. In what way is it not working? Post your SQL code again.

Brian
__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
Old 10-28-2013, 06:22 AM   #13
Nano
Newly Registered User
 
Join Date: May 2012
Posts: 91
Thanks: 26
Thanked 1 Time in 1 Post
Nano is on a distinguished road
Re: Ignoring null criteria in a multi-criteria query…

Here is the full SQL code: (Note: I have changed the names of the fields to protect the data)

SELECT [Product Table].[Product ID], [Product Table].Barcode, [Product Types].Model, [Product Types].[Color Group], [Product Types].Size, [Product Table].[%], [Product Table].Solution, [Product Table].Quantity, [Product Table].Unit, [Product Table].[Created by], [Product Table].[Date Created], [Product Table].[Date Updated], [Product Table].Location, [Product Table].[Current Lot Number], [Product Table].Retired
FROM [Product Types] INNER JOIN [Product Table] ON [Product Types].[Product ID Code] = [Product Table].[Product ID]
WHERE ([Forms]![Imput CSG]![ModelCB] = ModelCB OR [Forms]![Imput CSG]![ModelCB] Is Null) AND ([Forms]![Imput CSG]![ColorCB] = ColorCB OR [Forms]![Imput CSG]![ColorCB] Is Null) AND ([Forms]![Imput CSG]![SizeCB] = SizeCB OR [Forms]![Imput CSG]![SizeCB] Is Null);
Nano is offline   Reply With Quote
Old 10-28-2013, 04:51 PM   #14
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,938
Thanks: 13
Thanked 4,051 Times in 3,987 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Ignoring null criteria in a multi-criteria query…

You appear to have used the combo names instead of the field names. Try

SELECT [Product Table].[Product ID], [Product Table].Barcode, [Product Types].Model, [Product Types].[Color Group], [Product Types].Size, [Product Table].[%], [Product Table].Solution, [Product Table].Quantity, [Product Table].Unit, [Product Table].[Created by], [Product Table].[Date Created], [Product Table].[Date Updated], [Product Table].Location, [Product Table].[Current Lot Number], [Product Table].Retired
FROM [Product Types] INNER JOIN [Product Table] ON [Product Types].[Product ID Code] = [Product Table].[Product ID]
WHERE ([Forms]![Imput CSG]![ModelCB] = Model OR [Forms]![Imput CSG]![ModelCB] Is Null) AND ([Forms]![Imput CSG]![ColorCB] = [Color Group] OR [Forms]![Imput CSG]![ColorCB] Is Null) AND ([Forms]![Imput CSG]![SizeCB] = Size OR [Forms]![Imput CSG]![SizeCB] Is Null);
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Nano (10-29-2013)
Old 10-29-2013, 01:00 AM   #15
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 538 Times in 520 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
Re: Ignoring null criteria in a multi-criteria query…

I concur with Paul, I think that this would be more easily avoided if the criteria were typed as laid out by Paul in post #5

Brian

__________________
What is this life if, full of care,
We have no time to stand and stare

I do not have Access these days 2015
Brianwarnock is offline   Reply With Quote
The Following User Says Thank You to Brianwarnock For This Useful Post:
Nano (10-29-2013)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function ignoring criteria! Les Isaacs Modules & VBA 8 06-06-2012 11:21 AM
Null criteria in Query Edgarr8 Queries 4 05-30-2009 09:53 AM
Multi Select ListBox criteria in query oihjk Forms 1 04-01-2003 03:58 PM
enter multi criteria to display report if blank criteria select all records! frangipani Forms 1 02-11-2003 06:40 AM
[SOLVED] using multi-select on a listbox to set query criteria criteria kom73 Forms 1 10-10-2001 03:45 AM




All times are GMT -8. The time now is 06:42 PM.


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

Featured Forum post


Sponsored Links


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