Query AND vs. Or

[Guest]

I'm trying to exclude certain records i.e. containing either red, white or
blue in one field.
can someone explain

Not like "red" AND Not like "white" AND Not like "blue"

VS

Not like "red" Or Not like "white" Or Not like "blue"

I want to understand which I should be using and most importanly the logic
why.

 

[Guest]

The "and" in your statement will exclude all 3 "red", "white", and "blue".
The "or" in your statementr has the potential to exclude one and allow the
others
hope this helps

 

[Allen Browne]

An AND is true only if both parts are true.
To satisify the condition:
Not "red" And Not "blue"
the field would have to satisify both conditions.
Both red and blue are out.

An OR is true if either part is true.
To satisify the condition:
Not "red" OR Not "blue"
the field us accepted if it contains anything other than red, and it is also
accepted if it contains anything other than blue. Since "blue" is a value
other than "red" it satisfies the first half the the condition, and so it is
accepted. Likewise, "red" satisfies the 2nd part of the condition, since red
is not blue. So, but red and blue (and other colors) all pass. The condition
does nothing at all (except filter out the Nulls.)

 

[Michel Walsh]

Hi,


Another way to see it is if you want only those that have either red, white,
or blue, then:


IN('red', 'white', 'blue')


would do. If you want the complement, use


NOT IN('red', 'white', 'blue')



Hoping it may help,
Vanderghast, Access MVP