Where Not

[fipp]

I am trying to run a query on a set of data where [evalpos] does not contain
"P" Or "K"

Here is what I have.

SELECT specialists.*, specialists.last, specialists.first, specialists.evalpos
FROM specialists
WHERE (((specialists.evalpos)<>"P" & "Or"<>"K"))
ORDER BY specialists.last, specialists.first;

In the Microsoft windown under where I also tried the following (Not "P" Or
"K") this didn't work either? Any help would be great.

I can get it to work with one at a time but not both.

 

[Jeff Boyce]

When you tell Access to look for not "P" or not "K", it will find "P"
(because that one is NOT "K"!)

You want not "P" AND not "K" (neither of these, right?)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I am trying to run a query on a set of data where [evalpos] does not contain
"P" Or "K"

Here is what I have.

SELECT specialists.*, specialists.last, specialists.first, specialists.evalpos
FROM specialists
WHERE (((specialists.evalpos)<>"P" & "Or"<>"K"))

This will actually generate an expression

(specialists.evalpos) <> "POr" <> "K"

which is legal but nonsensical.

ORDER BY specialists.last, specialists.first;

In the Microsoft windown under where I also tried the following (Not "P" Or
"K") this didn't work either? Any help would be great.

I can get it to work with one at a time but not both.

OR is a Boolean algebra operator, just as + or - is an arithmatic operator.
Unfortunately it looks just like an English language conjunction - but it
doesn't work quite the same!

The syntax would be

WHERE evalpos <> "P" OR evalpos <> "K"

if you really wanted to use OR - but you don't!

If Evalpos is in fact equal to K then the first expression will be TRUE and
the second expression will be FALSE. The OR operator takes two logical
expressions and returns TRUE if either of them (or both of them) are TRUE. So
this criterion will return all records in the table; the ones that contain P
because that value is not equal to K, the ones that contain K because that
value is not equal to P, and the ones that contain X because both criteria are
true.

What you want is either to use AND - i.e. you want to retrieve the record if
it is simultaneously true that the field is not equal to P and also that it is
not equal to K:

WHERE (((specialists.evalpos) <> "P" AND (specialists.evalpos) <> "K")))

or more compactly, use the NOT IN clause:

WHERE (((specialists.evalpos) NOT IN ("P", "K")))