Solved

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

Posted on 2009-07-14
6
486 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:trbaze
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24855595
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"
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 24855607
If my previous suggestion did not work, then state what the WHERE criteria should be *using sentences*.
0
 
LVL 3

Author Closing Comment

by:trbaze
ID: 31603553
Your suggestion worked.  Thank you very much!  Why wouldn't the "OR" work?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Expert Comment

by:dinomix
ID: 24855652
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"));
 
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 24855664
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
0
 
LVL 3

Author Comment

by:trbaze
ID: 24855678
Ok.  I understand.  Thanks again for your help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now