Using Not (or <>) in SQL Statement

G

Guest

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#))
 
G

Guest

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.

Amanda Guenthner said:
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#))
Click to expand...
 
G

Guest

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.

Ofer said:
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.

Amanda Guenthner said:
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#))
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Then use

AND Won_May.OppID Not In (...)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Amanda Guenthner said:
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.

Ofer said:
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.

Amanda Guenthner said:
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#))
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

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.
Click to expand...

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]
 
Top