Build a filter query with AND and OR statement

[Gari]

Hello,

I am trying to build a filter query with some AND and OR.

I have three text boxes and 5 check boxes. The checkboxes are linked via code to other textboxes for the purpose of the query.

The first three text boxes are:
[CompName]
[AOName]
[GroupName]

The other five text boxes are:
[WillNot]
[Contacted]
[Promised]
[Opened]
[NotContacted]

They are all on a form and the result form on which is applied the filter query is on a sub form.

I want the user to be able to filter the data with [CompName], [AOName] & [GroupName] being all linked by AND (one excludes the other).

Then, in the meantime, the user will tick some of the five check boxes, which will activate some of the text boxes (they are not visible to the user), and all this should be OR linked (we can have some data which answers two or more of these criteria).

If there are no matching records, the query will return no results (or at best have a msgbox(“No results found”)).

My query is as follows:

SELECT tblProspects.CompName, tblProspects.Status, tblProspects.AOName, tblProspects.GroupName, tblProspects.InputDate
FROM tblProspects
WHERE (((tblProspects.CompName)=[Forms]![frmMgtMain]![CompName]) AND
((tblProspects.AOName)=[Forms]![frmMgtMain]![AOName]) AND
 ((tblProspects.GroupName)=[Forms]![frmMgtMain]![GroupName]) AND
 ((tblProspects.Status)=[Forms]![frmMgtMain]![WillNot] Or
 (tblProspects.Status)=[Forms]![frmMgtMain]![Contacted] Or
 (tblProspects.Status)=[Forms]![frmMgtMain]![Promised] Or
 (tblProspects.Status)=[Forms]![frmMgtMain]![Opened] Or
 (tblProspects.Status)=[Forms]![frmMgtMain]![NotContacted]))
GROUP BY tblProspects.CompName, tblProspects.Status, tblProspects.AOName, tblProspects.GroupName, tblProspects.InputDate;

But it does not work the way I want: the OR criteria are recognized and work well, whereas it does not even take into account the AND criteria.

Can someone help?

Thank you very much and best regards.

G.

[Rabbit]

With AND, the problem comes in when the user leaves it blank, i.e. NULL. It won't match up on any field. You have to account for it by using Like Variable & "*". However, this brings in the problem of pulling in similar records which you may not want.

[Gari]

Hello,

Thank you for the reply. Actually I already use the "*" as a default value for all the text boxes. The problem with my AND statements is that it is simply not working. Nothing happens, whereas for the OR stuff, it works dramatically well. Would it be of any use to build a nested query?

[Rabbit]

There's nothing wrong that I can see with the code. What do you mean by it doesn't work?

Can you provide some sample records, values in the text boxes, and the results that you get as opposed to the results that you should get?

[Gari]

The behaviour of the program is like that:

1. When you open the page, nothing is selected and all text boxes contain the "*" star. We thus expect all the data to appear. However, result is blank. Nothing at all appears.

2. We can play with whatever criteria from [CompName], [AOName] & [GroupName] (the AND filter), no results at all are returned. For instance if we select a company name, still no results will appear.

3. We can play with the criteria [WillNot], [Contacted], [Promised], [Opened], [NotContacted] (the OR statement), and thus effectively the query returns the results containing the criteria we have selected.

4. We cannot filter again the result of 3. using [CompName], [AOName] & [GroupName].

5. When a criteria from 3. is deselected, I don't know for what reason, but 3 results are still appearing with their status being a star "*" (like if the program had replaced their status with *).

I may attach a simple version of my db if you wish.

Thank you for your attention and best regards.

G.
(btw, I just double checked the names and they are correct)

[Gari]

Here are some examples:

Let’s say I have those data (I did not take into account [CompName] and [GroupName] as the behaviour would be the same as [AOName]):

[John Doe] [Not Contacted]
[Emeveret Dupit] [Contacted]
[Owen Solep] [Promised]
[John Doe] [Will Not]
[Emeveret Dupit] [Opened]
[John Doe] [Not Contacted]
[Emeveret Dupit] [Contacted]
[Owen Solep] [Promised]
[John Doe] [Will Not]
[Emeveret Dupit] [Opened]
[Owen Solep] [Not Contacted]
[John Doe] [Contacted]
[Emeveret Dupit] [Promised]
[Owen Solep] [Will Not]
[John Doe] [Opened]
[Emeveret Dupit] [Not Contacted]
[Owen Solep] [Contacted]
[John Doe] [Promised]
[Emeveret Dupit] [Will Not]
[Owen Solep] [Opened]

1. First, When I open the page, all the criteria are at “*”, so I should see everything. However, no results are returned.

2. Let’s say I tick the [Not Contacted] and [Contacted] checkboxes. I get the following:

[John Doe] [Not Contacted]
[Emeveret Dupit] [Contacted]
[John Doe] [Not Contacted]
[Emeveret Dupit] [Contacted]
[Owen Solep] [Not Contacted]
[John Doe] [Contacted]
[Emeveret Dupit] [Not Contacted]
[Owen Solep] [Contacted]

3. Now I want to filter again to see only the customers of John Doe. I should get:

[John Doe] [Not Contacted]
[John Doe] [Not Contacted]
[John Doe] [Contacted]

Whereas actually I get:

[John Doe] [Not Contacted]
[Emeveret Dupit] [Contacted]
[John Doe] [Not Contacted]
[Emeveret Dupit] [Contacted]
[Owen Solep] [Not Contacted]
[John Doe] [Contacted]
[Emeveret Dupit] [Not Contacted]
[Owen Solep] [Contacted]

Means: the filter is not applying.

4. Now I deselect [Not Contacted] and [Contacted], I get

[John Doe] [*]
[Emeveret Dupit] [*]
[Owen Solep] [*]

Three values that do not move anymore, with their status replaced by a star *. Which actually is not the expected behaviour (I should go back to the initial list).

5. If then I check again the check box [Not Contacted], I would get:

[John Doe] [*]
[Emeveret Dupit] [*]
[Owen Solep] [*]
[John Doe] [Not Contacted]
[Owen Solep] [Not Contacted]
[Emeveret Dupit] [Not Contacted]

Whereas I should get:

[John Doe] [Not Contacted]
[John Doe] [Not Contacted]
[Owen Solep] [Not Contacted]
[Emeveret Dupit] [Not Contacted]

Hope this was explicit.

Best regards,

G.

[Rabbit]

The behaviour of the program is like that:

1. When you open the page, nothing is selected and all text boxes contain the "*" star. We thus expect all the data to appear. However, result is blank. Nothing at all appears.

This is because you're not using Like. You're using = in your SQL code.

2. We can play with whatever criteria from [CompName], [AOName] & [GroupName] (the AND filter), no results at all are returned. For instance if we select a company name, still no results will appear.

Again this is most likely because you're using = instead of Like.

3. We can play with the criteria [WillNot], [Contacted], [Promised], [Opened], [NotContacted] (the OR statement), and thus effectively the query returns the results containing the criteria we have selected.

I don't know how this could work unless you took the CompName, AOName, and GroupName out of the SQL statement. What is WillNot, Contacted, Promised, Opened, and NotContacted? It sounds like they're checkboxes while the field is a string or coded numer, in which case I can't see how this would work.

4. We cannot filter again the result of 3. using [CompName], [AOName] & [GroupName].

This is probably because you the Like versus = issue.

5. When a criteria from 3. is deselected, I don't know for what reason, but 3 results are still appearing with their status being a star "*" (like if the program had replaced their status with *).

I have no idea about this one, maybe you have some records in there with * as their value.

[Gari]

I have worked out this:

SELECT tblProspects.CompName, tblProspects.Status, tblProspects.AOName, tblProspects.GroupName, tblProspects.InputDate
FROM tblProspects
WHERE (((tblProspects.CompName) Like [Forms]![frmMgtMain]![CompName]) AND ((tblProspects.AOName) Like [Forms]![frmMgtMain]![AOName]) AND ((tblProspects.GroupName) Like [Forms]![frmMgtMain]![GroupName]) AND ((tblProspects.Status)=[Forms]![frmMgtMain]![WillNot])) OR (((tblProspects.CompName) Like [Forms]![frmMgtMain]![CompName]) AND ((tblProspects.AOName) Like [Forms]![frmMgtMain]![AOName]) AND ((tblProspects.GroupName) Like [Forms]![frmMgtMain]![GroupName]) AND ((tblProspects.Status)=[Forms]![frmMgtMain]![NotContacted])) OR (((tblProspects.CompName) Like [Forms]![frmMgtMain]![CompName]) AND ((tblProspects.AOName) Like [Forms]![frmMgtMain]![AOName]) AND ((tblProspects.GroupName) Like [Forms]![frmMgtMain]![GroupName]) AND ((tblProspects.Status)=[Forms]![frmMgtMain]![Opened])) OR (((tblProspects.CompName) Like [Forms]![frmMgtMain]![CompName]) AND ((tblProspects.AOName) Like [Forms]![frmMgtMain]![AOName]) AND ((tblProspects.GroupName) Like [Forms]![frmMgtMain]![GroupName]) AND ((tblProspects.Status)=[Forms]![frmMgtMain]![Promised])) OR (((tblProspects.CompName) Like [Forms]![frmMgtMain]![CompName]) AND ((tblProspects.AOName) Like [Forms]![frmMgtMain]![AOName]) AND ((tblProspects.GroupName) Like [Forms]![frmMgtMain]![GroupName]) AND ((tblProspects.Status)=[Forms]![frmMgtMain]![Contacted]))
GROUP BY tblProspects.CompName, tblProspects.Status, tblProspects.AOName, tblProspects.GroupName, tblProspects.InputDate;

Which seems to work like I want.

Thank you for your time and best regards.

G.

[Rabbit]

Glad you got it working. Good luck.