Solved

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

Posted on 2009-07-14
6
508 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 33
Email Header Detail 12 55
MS Access 2010 Close Form  Event - Stop Form Closing 4 27
MS access 2010. Trouble on exporting to excel 2010 13 8
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 …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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