Using Not (or <>) in SQL Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amanda Guenthner - 07 May 2005 23:52 GMT

Hi,

I am trying to run a query that excludes records with specified IDs (OppID)
in this case. Everything in the query seems to work except for the following
part:

AND ((Won_May.OppID)<>"VER-MB65-0393" Or (Won_May.OppID)="RED-JM35-8869" Or
(Won_May.OppID)="MCI-KB79-1814" Or (Won_May.OppID)="BEL-SH77-7592" Or
(Won_May.OppID)="BEL-JA21-6686" Or (Won_May.OppID)="COM-JP56-3020" Or
(Won_May.OppID)="BEL-MB11-2558" Or (Won_May.OppID)="EMC-MF61-6152" Or
(Won_May.OppID)="BEL-SB43-0007" Or (Won_May.OppID)="BEL-SD17-2938" Or
(Won_May.OppID)="MCG-MP74-8341" Or (Won_May.OppID)="SAP-PH15-4322")

This strikes me as odd, because I have another clause where I use not to
exlude certain values in another field. Additionally, when I do a find on
the above specified IDs (i.e. "BEL-MB11-2558") all are returned with the
exception of the first specified ID (VER-MB65-0393"); so I am wondering if I
am incorrectly using <> when trying to exclude multiple values.

I have pasted my entire SQL statement below for reference. Any help would
be much appreciated!

Thx,
Amanda
__________________________________________________

SELECT *

FROM Won_May

WHERE (((Won_May.SL)="Customer Relationship Management")

AND ((Won_May.Domain)="Customer Contact Transformation (CCT/ACCS)" Or
(Won_May.Domain)="Customer Contact Transformation" Or
(Won_May.Domain)="Customer Contact Transforamtion (Scale Play)" Or
(Won_May.Domain)="Customer Interaction")

AND ((Won_May.[Campaign/ Big Initiative])<>"IT Outsourcing" Or
(Won_May.[Campaign/ Big Initiative])="Sales Transformation" Or
(Won_May.[Campaign/ Big Initiative])="Network Optimization" Or
(Won_May.[Campaign/ Big Initiative])="HR Outsourcing" Or (Won_May.[Campaign/
Big Initiative])="F&A Outsourcing" Or (Won_May.[Campaign/ Big
Initiative])="Billing Transformation Outsourcing")

AND ((Won_May.OppID)<>"VER-MB65-0393" Or (Won_May.OppID)="RED-JM35-8869" Or
(Won_May.OppID)="MCI-KB79-1814" Or (Won_May.OppID)="BEL-SH77-7592" Or
(Won_May.OppID)="BEL-JA21-6686" Or (Won_May.OppID)="COM-JP56-3020" Or
(Won_May.OppID)="BEL-MB11-2558" Or (Won_May.OppID)="EMC-MF61-6152" Or
(Won_May.OppID)="BEL-SB43-0007" Or (Won_May.OppID)="BEL-SD17-2938" Or
(Won_May.OppID)="MCG-MP74-8341" Or (Won_May.OppID)="SAP-PH15-4322")

AND ((Won_May.[Closed Date])>#9/1/2004#))

Reply to this Message

Ofer - 08 May 2005 00:24 GMT

I would try that
AND Won_May.OppID in("RED-JM35-8869"
,"MCI-KB79-1814","BEL-SH77-7592","BEL-JA21-6686" ,"COM-JP56-3020",
"BEL-MB11-2558","EMC-MF61-6152","BEL-SB43-0007","BEL-SD17-2938","MCG-MP74-8341","SAP-PH15-4322")

I don't think you need that part
((Won_May.OppID)<>"VER-MB65-0393" You specified what you wont Won_May.OppID
to be equal to, so you dont have to specifiy what you dont want it to be
equal to.
by Saying ((Won_May.OppID)<>"VER-MB65-0393" its meen bring everything exept
VER-MB65-0393 so all the equal statement are eralavent.

> Hi,
>
[quoted text clipped - 48 lines]
>
> AND ((Won_May.[Closed Date])>#9/1/2004#))

Reply to this Message

Amanda Guenthner - 08 May 2005 00:41 GMT

Sorry - to clarify - I do not want records w/ these IDs. In short - I want
all records that satisfy my other conditions, but only if they are not = to
the provided IDs.

> I would try that
> AND Won_May.OppID in("RED-JM35-8869"
[quoted text clipped - 60 lines]
> >
> > AND ((Won_May.[Closed Date])>#9/1/2004#))

Reply to this Message

Douglas J. Steele - 08 May 2005 01:31 GMT

Then use

AND Won_May.OppID Not In (...)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Sorry - to clarify - I do not want records w/ these IDs. In short - I
> want
[quoted text clipped - 78 lines]
>> >
>> > AND ((Won_May.[Closed Date])>#9/1/2004#))

Reply to this Message

John Vinson - 08 May 2005 03:30 GMT

>Hi,
>
[quoted text clipped - 14 lines]
>exception of the first specified ID (VER-MB65-0393"); so I am wondering if I
>am incorrectly using <> when trying to exclude multiple values.

Yes, you are.

The reason is that if the OppID is in fact equal to "VER-MB65-0393"
then the first OR statement is false - but all of the rest are TRUE!
Since the logical expression (A OR B) returns TRUE if either A or B is
true, your expression above will inevitably be TRUE.

Use the syntax

AND Won_May.OppID NOT IN("VER-MB65-0393", "RED-JM35-8869", ...)

John W. Vinson[MVP]

Reply to this Message