Excluding multiple criteria in a field

  • Thread starter Thread starter fred w
  • Start date Start date
F

fred w

I am trying to create a query that does not return values
of "Complete" and "Cancelled". If enter <> "Complete" the
query returns everything but "Complete" but if I try to
combine the two with an OR, I get all values in the field.

I appreciate the Help!

fred w
 
You could use the logic:
Not In ("Complete","Cancelled")

That usually works for me.

HTH
SteveE
 
I am trying to create a query that does not return values
of "Complete" and "Cancelled". If enter <> "Complete" the
query returns everything but "Complete" but if I try to
combine the two with an OR, I get all values in the field.
Click to expand...

That's because OR is a Boolean logical operator, not an English
language conjunction! Your expression makes sense in language but
computers are literal minded: if you say

[Field] <> "Complete" OR [Field] <> "Cancelled"

and the field does in fact contain the text string Cancelled, Access
will evaluate the first expression and say "ok, that's true,
'Cancelled' is not equal to 'Complete'"; then it will evaluate the
expression [Field] <> "Cancelled" and find that it is false.

The OR operator will then say "True OR False" - if either or both
expressions are True, return True.

So it will return TRUE and retrieve the record!

Use AND instead, or (more efficiently)

NOT IN("Complete", "Cancelled")
 
Back
Top