Ignoring null criteria in a multi-criteria query…

[Nano]

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]

One way:

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

 

[Nano]

Thanks works great!

 

[Nano]

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

 

[pbaldy]

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]

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]

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]

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

 

[Nano]

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

 

[pbaldy]

Can you post the db here?

 

[Nano]

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]

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]

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]

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]

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]

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]

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