Solved Queries Parameter Include AND Exclude (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
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

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
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

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
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

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
Yes - put some brackets around the bits between the "AND" sections to make sure they are evaluated correctly.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
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

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
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

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
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

1634201332892.png
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
I updated the query to have all the criteria as above, and it works perfectly, but has changed the query to look as below.

1634201851814.png


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

~Matt
 

Minty

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
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

AWF VIP
Local time
Today, 01:10
Joined
Jul 26, 2013
Messages
10,355
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

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
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

..forever waiting... waiting for jellybean!
Local time
Today, 09:10
Joined
May 7, 2009
Messages
19,169
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

Self Taught, Learn from the Forums
Local time
Today, 01:10
Joined
Feb 5, 2019
Messages
292
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
 

Users who are viewing this thread

Top Bottom