Pass a filter criteria to a query that include both wildcard and Null (1 Viewer)

Hi,
I have been looking around for a while now trying to figure this out. Would really appreciate some assistance. I have a form with some combos to allow the user to pass filter criteria to a query. When one combo is left empty I want this criteria to be disregarded in the filtering - meaning all values (and null) should be included. I handle this through some code where I use the following approach; "If nz(FilterCombo; "") <> "" then FilterCombo Else "*" (please consider this is conceptual code only). I add the outcome to a public parameter and in the query I use "Like Get_FilterParameter()". This works great with one exception. The wildcard filters out all Null values.

If I add "Or Is Null" directly in the query filter criteria I will always include the Null values even if the user actively selected a criteria so that doesn't do it either.

Can I pass a different criteria (instead of my wildcard) that will do this for me?

Any directions would be very appreciated!

Thanks!

Depends on whether your function returns Null or not, so you would need to re-factor your code if it doesn't.

The idea is basically this:

Hi
This is how I would construct the SQL

This code is for the full query but you can adapt it for a filter

HTH

T

Many thanks for all the great input! The way you describe it, Tim is how I would do it for a form filter and just apply the strWhere to the Me.Form.Filter. I'm not a wizard at this so maybe there is an obvious answer to it - but could I apply this strWhere to a static query? Meaning could I go into the SQL view of my existing query and exchange the lengthy code under the Where clause with this VBA defined string (strWhere)? That would be a great way for me to do what I need. Any syntax guiding would be very appreciated!

Now I'm just passing a number of public parameters to the static query and that doesn't allow me to disregard the filter if it is not specified by the user - which this method totally does...

Would the approach vbaInet suggests allow me to do this? What I would look for with that approach is to change my current code;

If Nz(Me.cboStatus, "") <> "" Then
MyfltrStatus = Me.cboStatus
Else
MyfltrStatus = "*"
End If
(this comes in multitude for a number of different combos and filter criteria)

to something like;

If Nz(Me.cboStatus, "") <> "" Then
MyfltrStatus = Me.cboStatus
Else
MyfltrStatus = "* Or Is Null"
End If

If that is what I could accomplish with the approach from vbaInet I would really appreciate a bit more guidance to fully understand it.

Hi Again
Re-reading your original post, I don't know what you are using your query for..
You can dynamically change a static query. But what are you using the query for?
If your using it for a record source you can use the .RecordSouce = mySQL driven by the After update event on the combo box.
If the query is to set up another combo box you can use me.cboBox.RowSource = mySQL.
for example
I hope this helps

I'm using the query for a standard report in Access and for a data dump to a spreadsheet. So with this approach I would not really use the static query as such right? Just defining it in the code (including the strWhere string) and then update the data source for the report (and excel export)? It sounds much more straight forward... I guess my limitations in SQL kept me to the long path.

Good luck..
We all had to start somewhere and I am still learning. There are always more than one way to do things. Its finding what's best.

T

There are always pros and cons of using certain methods, in this case where it's just two criteria it seems a bit excessive to build it in strings. Besides that you're not getting the benefit of JET's Rushmore optimisation and you're having to handle dates and Strings. Whereas in a query, you're leveraging Rushmore and JET handles your Dates and Strings for you as long as you declare those parameters.

I would use the Is Null check mentioned in my previous post for a simple criteria such as this, and for a more complex criteria build the string like TimW suggested.