Ignoring null criteria in a multi-criteria query… (1 Viewer)

Nano · Oct 24, 2013

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?

pbaldy · Oct 24, 2013

One way:

http://access.mvps.org/access/queries/qry0001.htm

Nano · Oct 24, 2013

Thanks works great!

Nano · Oct 24, 2013

Well maybe not, now it brings back all of the records even if it doesn't meet the other criteria

pbaldy · Oct 24, 2013

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)

Nano · Oct 25, 2013

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)

pbaldy · Oct 25, 2013

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)

Nano · Oct 25, 2013

Yes the form is open when running this query.
Ok, I will fix that part, that is likely my problem.

Nano · Oct 25, 2013

No sadly it is still not working. I might have to try something else like an iif statement.

pbaldy · Oct 25, 2013

Can you post the db here?

Nano · Oct 25, 2013

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.

Brianwarnock · Oct 26, 2013

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

Nano · Oct 28, 2013

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);

pbaldy · Oct 29, 2013

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);

Brianwarnock · Oct 29, 2013

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

Nano · Oct 29, 2013

pbaldy I tried your code, but it still didn't work. So I am going with my back up plan, as I don't have much more time to work on this. Yes I know this is bad coding, but I created a few different queries with the filter for each possible outcome of filling out the combo boxes. I have tested each query and it does work correctly however. I am not sure how to write a IIf statement in a case when some combo boxes are null and others are not.

I do have some ideas on how to work around this, but if there is a do this fixing this statement it would help me.

If: IsNull([ModelCB]) & IsNull([ColorCB]) & Not IsNull([SizeCB]) then
Open query --+...

Nano · Oct 29, 2013

I have it working now, I used the idea above. Thank you for the help

Ignoring null criteria in a multi-criteria query… (1 Viewer)

Nano

Registered User.

pbaldy

Wino Moderator

Nano

Registered User.

Nano

Registered User.

pbaldy

Wino Moderator

Nano

Registered User.

pbaldy

Wino Moderator

Nano

Registered User.

Nano

Registered User.

pbaldy

Wino Moderator

Nano

Registered User.

Brianwarnock

Retired

Nano

Registered User.

pbaldy

Wino Moderator

Brianwarnock

Retired

Nano

Registered User.

Nano

Registered User.

Similar threads

Users who are viewing this thread