how to query multiple criteria when one or more are null

[JimL]

Good day, all.

I have a form that has multiple fields from which to include in a query, but can only query on one criterion at a time. I would like to query on any combination of the criteria. This would include the possibility of some fields being blank.

I would appreciate suggestions on how to write the query. If there are any additional questions, please let me know.

Thanks!

JimL

 

[plog]

For situations like this I write a query with no criteria. I use other methods to filter the data upon a button press.

So, this means we need to know how the query's data is intended to be consumed. Usually, I make a report based upon that query. The user then completes the selection form and presses the button. The button then constructs a filter string and uses it in a DoCmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) to open the report to just the records that meet the criteria.

This method can also be used to export the query to Excel or Open a form. It really depends on how the data is to be consumed.

 

[JimL]

Thanks. Can I filter on numerous criteria in the report?

 

[Deleted member 147267]

I think the typical approach is to concatenate the criteria with the SELECT and WHERE clauses in code and build the sql. What you do with that depends on the purpose of the sql; e.g. execute it (action query), assign it to the sql source of an existing query, assign to a recordset, etc.

In this method, you concatenate criteria A or A&B or A&C or B&C or just C, etc. to the first part then concatenate anything that comes after, such as ORDER BY clause.

 

[JimL]

I probably stated the request incorrectly.

I have a database that has fields:

Store
Sales Category
Features Category
Product name
Order

I would like to be able to have a form that uses combo lists showing stores, sales categories, and product name to drill down as needed. I cannot figure out how to write the query or create a report with the necessary filters.

 

[Gasman]

Something along these lines?

http://allenbrowne.com/ser-62.html

 

[plog]

Can I filter on numerous criteria in the report?

Yes. You build your filter string based on the inputs used on the form. The pseudo code would look like this:

str_Filter="(1=1)" 

if IsNull(Input1)=False Then str_Filter = str_Filter & " AND ([Field1] = '" & Input1 & "')"
if IsNull(Input2)=False Then str_Filter = str_Filter & " AND ([Field2] = '" & Input2 & "')"

...

 

[Deleted member 147267]

I would like to be able to have a form that uses combo lists showing stores, sales categories, and product name to drill down as needed

I'm a very literal person so I don't know if that is about filtering form records based on combos or just filtering combo lists based on selections from other combos. If the latter, then what you want is commonly referred to as cascading combos so I doubt that anything presented thus far is applicable. Especially since you said you might have misrepresented the issue...

 

[isladogs]

I have two examples that may be helpful to you
1. An example app that shows how multiple combos can be used to filter data including where some vales may be null
https://www.access-programmers.co.uk/forums/showthread.php?t=302189
Look at the Student Target Grade Explorer app
2. An example using cascading combos to gradually filter data step by step Postcode Builder

Hope one or both is helpful