Not Or Statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, Please help...

I am struggling with something really simple today, I want to write in my
query if Names appear in the data not to show them.
eg
Not "Jez"
but when I dont want multiple names like
Not "Jez" Or Not "Dan" it doesnt work, still brings back results for the
second part of the Not Statement. How can I get round that when I have more
than one thing I dont want in my query?

Heres hoping :-)

Jez
 
You could use the 'IN' keyword. Here's an example that uses the Customers
table from the Northwind sample MDB ...

SELECT Customers.CustomerID
FROM Customers
WHERE (((Customers.CustomerID) NOT IN ('ALFKI','BERGS','CACTU')));
 
As others have said, the IN clause is the best solution to your problem.
However, I'd like to address why your original criteria did not work.

The Not operator is like a negative sign in a math equation, it reverses
everything including the math operator. Consider this equation:
6 - 5 =1
this is equivalent to
(+6) + (-5) = 1
Note: within the parenthases, the + or - denotes the sign. Outside they are
math operators.

If you wanted to reverse this (ie add a negative sign to the front), you'd
do this:
-(6-5) = -1
which equates to this:
(-6) + (+5) = -1

The point to all this is that the negative sign reverses not only the sign
of each number, but ALSO reverses the math operator (- to +)

The same is true of logical operators AND and OR. They are opposites of each
other. So when you want to reverse this:
Jez or Dan
you can say:
Not (Jez or Dan)
or:
Not Jez AND Not Dan

That's what caused the original problem. The statement should have been:
Not "Jez" AND Not "Dan"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
the advise you received on using the IN() would be the correct way to do it.
As to your original problem, it would work if you used And rather than Or.

The way the logic works is that it sees Not "Jez" Or Not "dan". When is
finds Jez, is thinks, okay, it is not dan, so include it. When is sees dan
it thinks, okay it is not Jez so include it.

On the obverse, if you want to include only Jez and Dan, you would use.
"Jez" Or "Dan". The only Jez and Dan would be included in the output. If
you tried "Jez" And "Dan", you would get an empty because there is not record
that is both.

Note that Not logically reverses And and Or.
 
Hi,


It is context dependant. The query designer grid accepts 'incomplete'
syntax, such as =4 OR = 5, or such as >= 4 AND <=6. The designer
translates such 'incomplete' syntax, if you switch to the SQL view, to:
field >=4 AND field <=6. But sure, if you use the 'incomplete' syntax in,
says, a VBA-if statement, you are due to some surprise.

As stated by Klatuu, the original idea seems wrong though. (field <> 4) OR
(field <> 5) is always true (except against null), 'cause if your field
value is equal to 4, the <>5 evaluates to true and the OR evaluates also to
TRUE. A NOT IN(4, 5), or a <> 4 AND <> 5 is probably closer to what is
desired.


Vanderghast, Access MVP
 
Back
Top