Using NOT Operator with multiple critieria in a query (MS Access 2000)

  • Thread starter Thread starter deejayquai
  • Start date Start date
D

deejayquai

Hello

I'm trying to design a simple query which lists records by site. I'm
trying to set the criteria to NOT list those records from several
specific sites. I've been using the Not "1st site name" and then the
OR Row with Not "2nd site name" but it doesn't work. If I parse as
NOT "1st site" or "2nd site" all in one row then it doesn't work
either. Any ideas?

thanks for your help


D.
 
When you have multiple NOTs in a query, you need to separate them with AND
instead of OR.
 
In addition to Cheryl's sage advice...
it also sounds like you are using
the NOT operator?

The NOT operator performs a logical
negation of an expression.

Just making sure you are using "<>" instead.

WHERE [myfield] <> "1st site name"
AND [myfield] <> "2nd site name"

or

WHERE [myfield] NOT IN ('1st site name', '2nd site name')

Pardon me for butting in if I misunderstood.

Good luck,

Gary Walter
 
Thanks for expanding on my all too brief response. (Not enough Starbuck's
intake yet!)

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Gary Walter said:
In addition to Cheryl's sage advice...
it also sounds like you are using
the NOT operator?

The NOT operator performs a logical
negation of an expression.

Just making sure you are using "<>" instead.

WHERE [myfield] <> "1st site name"
AND [myfield] <> "2nd site name"

or

WHERE [myfield] NOT IN ('1st site name', '2nd site name')

Pardon me for butting in if I misunderstood.

Good luck,

Gary Walter


Cheryl Fischer said:
When you have multiple NOTs in a query, you need to separate them with AND
instead of OR.
Click to expand...
Click to expand...
 
I think

NOT ("1st site name" OR "2nd site name")

probably works in the Query Grid.

If you apply de Morgan's laws on the above criteria, you get what Cheryl and
Gary wrote.
 
Thanks Van,

I never fail to learn something...

placing

Not "A"

in criteria

got changed to

WHERE Not ([somefield]="A");

in the SQL stmt.

For some reason I thought

Not "A"

would become

WHERE (somefield) Not "A"

If it is smart enough to change

<>"A" And <>"B"

I should have realized it would be
smart enough to change the above.

I should have tested before replying.

/////////////////////
and, yes it did work,

Not ( "A" Or "B")

got changed in SQL stmt to

WHERE ((Not ((somefield)="A" Or (somefield)="B")));

which is logically equivalent of course.

Thanks again Van
 
Cheryl/Gary

I am often surprised by what the QBE can do (and sometimes by what the QBE
cannot do).

I remember once I had really convoluted criteria which I put in a criteria
"cell". After saving the Query and re-opening it in DesignView, I found
that Access created 2 extra calculated Fields and placed criteria on these
calculated Fields as well as leaving part of the original criteria on the
original Column.
 
Back
Top