how to query multiple criteria when one or more are null (1 Viewer)

[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?

 

[Micron]

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 & "')"

...

 

[Micron]

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

 

[Pat Hartman]

Although if you are using Jet/ACE as the BE, you can get away with binding forms/reports to tables or to queries with no criteria, the best solution (especially if you now or in the future need to use SQL Server or other RDBMS) is to include criteria in queries.

There are two common ways to do this when you have multiple options, some of which are optional.
1. Build the WHERE clause on the fly and substitute a new query in the load event of the form/report
2. If you only have a few criteria, you can code them to be optional. The problem with this solution is that Access is going to attempt to help you and will rewrite your SQL string and make it completely unreadable. So, when I use this method, I switch my query to SQL view and NEVER, EVER switch it back to QBE view. I also paste a copy of the SQL in the code even though I don't build the string with code.

Where (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null)
AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null)
AND (fld3 = ....

Notice the pattern. This example shows THREE compound conditions. Each compound conditon is satasfied by a match to the form field OR the form field being null) Each compound condition MUST be enclosed in parentheses so they will be evaluated as I intend.

In boolean logic, AND takes presidence over OR so you have to be careful:

a = b or c = d and e = f

will be evaluated as:

a = b or (c = d and e = f)

Which will produce different results than:

(a = b or c = d) and e = f

Which may be what you want.