Solved Queries Parameter Include AND Exclude

[MattBaldry]

Good morning all,

I am having an issue with a query where I want to filter a field based on 2 different drop downs. I want 1 drop down to filter ONLY by that customer or all customers if blank.

The second drop down I want it to filter that customer out or show all customers if blank. I use a wild card as below for the include filter.

Like "*" & [Forms]![frmProductionManagement]![cboCustomerFilter] & "*"

I cannot seem to get anything to work on the exclude though. If it is blank, it shows no customers. If I select a customer, it works.

Where am I going wrong on the exclude?

On this query I have 10 fields that can be filtered on, but only the customer will have both include and exclude options.

In the past I have done this with a separate query for just the customer filter part, I was hoping there was a way to avoid this though.

~Matt

 

[Minty]

To filter to just the customer selected, remove the wild cards and use (no point using the wildcards you know they are in the list).

[Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null

To exclude do something very similar

NOT = [Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null

Both of these should handle when nothing is selected to return everything.

 

[MattBaldry]

To filter to just the customer selected, remove the wild cards and use (no point using the wildcards you know they are in the list).

[Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null

To exclude do something very similar

NOT = [Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null

Both of these should handle when nothing is selected to return everything.

Thanks Minty,

I had tried the first part yesterday and that worked fine. The second drop down is called [Forms]![frmProductionManagement]![cboCustomerExcludeFilter] so would I do something like below?

[Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null AND NOT = [Forms]![frmProductionManagement]![cboCustomerExcludeFilter] OR [Forms]![frmProductionManagement]![cboCustomerExcludeFilter] Is Null

~Matt

 

[Minty]

Yes - put some brackets around the bits between the "AND" sections to make sure they are evaluated correctly.

 

[MattBaldry]

Thanks Minty,

I had tried the first part yesterday and that worked fine. The second drop down is called [Forms]![frmProductionManagement]![cboCustomerExcludeFilter] so would I do something like below?

[Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null AND NOT = [Forms]![frmProductionManagement]![cboCustomerExcludeFilter] OR [Forms]![frmProductionManagement]![cboCustomerExcludeFilter] Is Null

~Matt

Tried the code as above and it works. Thanks Minty, it was so much more simple than my mind had it.

With regards to wildcards, if I am filtering on a dropdown should I always use similar parameters as above? Wild cards only for blank text boxes?

~Matt

 

[Minty]

If your drop down only has valid entries in it (or is linked to a primary key that is in the underlying forms recordsource) there is absolutely no point in using wild cards, they simply slow down the response.
If you add a wildcard to the beginning of a text string, Access can't utilise any indexes in the search, so they become very slow on large datasets.

So even in a partial string search text box try and avoid them at the start or train your users to add one to their search terms if they really need to look anywhere for *Ray

 

[MattBaldry]

Update, it now doesn't work, and it has changed my query quite a lot. I put the code in as below, but is changes the query.

[Forms]![frmProductionManagement]![cboCustomerFilter] OR [Forms]![frmProductionManagement]![cboCustomerFilter] Is Null

It now adds a field at the end of the query with the Show Box not ticked and 2 options Is Null then under that is blank rather than leave the code in the criteria under the Customer field.

I have some more tidying to do on this, but you can see the field on the right it has added.

Is this due to the Check Box filter I also have for the Due Date field?

~Matt

 

[MattBaldry]

I updated the query to have all the criteria as above, and it works perfectly, but has changed the query to look as below.

It has added the Is Null parts on the right for every parameter I want. Is this normal?

~Matt

 

[Minty]

Yes - the query designer does expand things out in an interesting way, visually the way the designer works it is spot on.
If you look at in SQL view the number of brackets will be eye-watering.

 

[Minty]

As an after-thought you could simply build the correct Where clause in VBA based on what was or wasn't selected, it's more involved but would simplify the resulting query.

Good VBA exercise if you need the practice and plenty of examples on here to work with, as well as some clever routines that try and encapsulate the process.

 

[MattBaldry]

As an after-thought you could simply build the correct Where clause in VBA based on what was or wasn't selected, it's more involved but would simplify the resulting query.

Good VBA exercise if you need the practice and plenty of examples on here to work with, as well as some clever routines that try and encapsulate the process.

I had been thinking about the VBA method, but it is not something I have ever tried before. I will have a look and see what I can find.

~Matt

 

[arnelgp]

I updated the query to have all the criteria as above, and it works perfectly, but has changed the query to look as below.

i could just imagine those "redundant" criteria.

 

[MattBaldry]

i could just imagine those "redundant" criteria.

I know what you mean, I think. I would love one of you VIPs to look over my work and show me better ways of doing the things I have done.

My database has been built over the years and I know most of what I have done will not be the best way, but I have learnt it all from Google and in these forums, and it works for what we need.

I just wish I had the time to learn proper ways and really streamline it to work better.

~Matt

 

[Minty]

TBH with one or two combo's the technique you have used is absolutely fine.
Once you get in more than that I would look at a more VBA Dynamic solution. Or....

Have a read of this thread - https://www.access-programmers.co.u...rm-filter-and-requires-almost-no-code.311279/