?
Solved

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

Posted on 2009-07-14
6
Medium Priority
?
532 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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