Why won't the Access query recognize multiple "Not" statements?

Query does not exclude any records unless I remove one of the Not statements.
Here is Code:
SELECT tblRecords.LivID, tblRecords.Sold, tblRecords.TagNo
FROM tblRecords
WHERE (((tblRecords.Sold)=False) AND (Not (tblRecords.TagNo)="0000-0001")) OR ((Not (tblRecords.TagNo)="0000-0003"));

Open in new window

Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
If my previous suggestion did not work, then state what the WHERE criteria should be *using sentences*.
Patrick MatthewsCommented:
WHERE tblRecords.Sold = False AND Not tblRecords.TagNo = "0000-0001" AND Not tblRecords.TagNo = "0000-0003"

although I prefer:

WHERE tblRecords.Sold = False AND tblRecords.TagNo <> "0000-0001" AND tblRecords.TagNo <> "0000-0003"
trbazeAuthor Commented:
Your suggestion worked.  Thank you very much!  Why wouldn't the "OR" work?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Not is a unary operator which reverses a boolean expression. The Not in your WHERE clause is being applied to the tblRecords.TagNo instead of the expression tblRecords.TagNo="0000-0001"

Try this:
WHERE tblRecords.Sold=False AND (Not (tblRecords.TagNo="0000-0001") OR Not (tblRecords.TagNo="0000-0003"));
Hi trbaze

If we make the WHERE clause easier to read by removing some brackets, we get:

WHERE ( (tblRecords.Sold=False) AND (Not tblRecords.TagNo="0000-0001") )
OR (Not tblRecords.TagNo="0000-0003")

This will return all records, because TagNo cannot be BOTH "0000-0001" AND "0000-0003".

I think what you really mean is:

WHERE (tblRecords.Sold=False)
AND (tblRecords.TagNo<>"0000-0001")
AND (tblRecords.TagNo<>"0000-0003")
Graham Mandeno - Access MVP
trbazeAuthor Commented:
Ok.  I understand.  Thanks again for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.