Filtering expression with multiple OR with one AND

[Guest]

I have created a search form where my user can select any or all of a number
of criteria. One of the criteria includes a date range. I am using the
filter with DoCmd.OpenReport when the user clicks a button on the form.
Currently my filter statement looks something like this:

"Status = 'Aproved' OR Status = 'Declined' AND Date = #1/1/2006#"

I want to make sure that the date is the most important criteria then look
for Approved or Declined items for that date. Any ideas?

Thanks,
Rich

 

[Guest]

Mixing AND and OR clauses in any criteria can be VERY confusing. The problem
is that AND and OR have the same ranking in operational order of precedance.
That means that your statement as written could interpreted as:

Give me all the records where both of the statements below are true
1) Status = 'Aproved' OR Status = 'Declined'
2) Date = #1/1/2006#"

On the other hand, it could also mean:

Give me all the records where EITHER of the statements below are true
1) Status = 'Aproved'
2) Status = 'Declined' AND Date = #1/1/2006#"

If it's not clarified, then the compiler figures it out as best as it can
depending on which comes first. Not a good thing.

In order to resolve this logically, you have to group the clauses together
with parantheses. In your case, interpreting your statement below as:

Give me all the records where both of the statements below are true
1) Date = #1/1/2006#"
2) Status = 'Aproved' OR Status = 'Declined'
(Which is logically the same as the first one above)

You would want to construct it as:

(Date = #1/1/2006#) AND (Status = 'Aproved' OR Status = 'Declined')

(BTW, you DO know that DATE is a reserved word, and should NOT be used as a
field or variable name, right? Use something like "MyDate" instead.)

Hope that helps!

David

 

[Guest]

David,

I appreciate the thorough explanation. I will rework my code to group the
items inside of parenthesis.

I simplified my filter string for the post. There are actually 3 different
dates that it could be filtering on and about 6 other criteria involved as
well. Now I need to do some work to figure out the logic.

Thanks again,
Rich

 

[Ron2005]

I disagree with the statement that AND and OR have equal ranking. It
depends on what you mean by that.

Parsing will catagorize everything after an OR as a different test
UNLESS you have something to group the tests.

examples:
statement 1
(Date = #1/1/2006#) AND (Status = 'Aproved' OR Status = 'Declined')
Results 1
will return all that have Date = #1/1/2006#" AND Status = 'Aproved'
and will return all that have Date = #1/1/2006#" AND Status =
'Declined'

Statement 2
(Date = #1/1/2006# AND Status = 'Aproved') OR Status = 'Declined'
Results 2
will return all that have Date = #1/1/2006#" AND Status = 'Aproved'
and will return all that have Status = 'Declined'

Statement 3
Date = #1/1/2006# AND Status = 'Aproved' OR Status = 'Declined'
Results 3
will return all that have Date = #1/1/2006#" AND Status = 'Aproved'
and will return all that have Status = 'Declined'

The whole key is anytime it encounters an OR then everything from the
begining to that OR
or from that OR to the end
or from that OR to the Next OR
will each be considered a separate test

***** UNLESS ***** the parentheses are used to group the tests.

And when you throw in a NOT you really have to be careful

If I said in english: "I want are records that do not have a code of A
or B" you would write it as

Not Code = "A" AND not Code = " B"

or

Not (Code = "A" or Code= "B")

because if you say

Not code= "A" or code = "B" you will get everything except code A.

(The first test will give you everything that is NOT A
and the code after the OR will give you all the B's but you already got
them with the first test.)


Be carefull and know your data and double check it when combining OR s
and NOT s.

Ron