eliminating criteria in a query

[pat67]

Hi, I have a query in which I am trying to eliminate data by way of
criteria. For example, I want to show the output where field 1 is not
null AND field 2 to does not equal a specific input AND field 3 does
not equal a specif input. I need the out put to show where only all 3
of these are met. What I am getting is an output showing where ANY of
these are met. Here is the line from the sql

WHERE ((Not (qrySE16_New.WBS) Is Null) AND ((qrySE16_New.MRPC)<>"TLS")
AND ((qrySE16_New.[Doc Type])<>"ID"));

Obviously I am doing it wrong. Please help. Thanks

 

[John Spencer]

I would try

WHERE qrySE16_New.WBS Is Not Null
AND qrySE16_New.MRPC)<>"TLS"
AND qrySE16_New.[Doc Type])<>"ID"


John Spencer
Access MVP 2002-2005, 2007-2011

 

[John W. Vinson]

Hi, I have a query in which I am trying to eliminate data by way of
criteria. For example, I want to show the output where field 1 is not
null AND field 2 to does not equal a specific input AND field 3 does
not equal a specif input. I need the out put to show where only all 3
of these are met. What I am getting is an output showing where ANY of
these are met. Here is the line from the sql

WHERE ((Not (qrySE16_New.WBS) Is Null) AND ((qrySE16_New.MRPC)<>"TLS")
AND ((qrySE16_New.[Doc Type])<>"ID"));

Obviously I am doing it wrong. Please help. Thanks

This is sort of a "double negative" problem - it's NOT Not Equal to one or
more of the criteria. Try

WHERE Not (qrySE16_New.WBS Is Null AND qrySE16_New.MRPC="TLS" AND
qrySE16_New.[Doc Type]="ID");

so it collect all of the conditions that you want to exclude, and if they are
all met, the NOT will reject that record.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

Hi, I have a query in which I am trying to eliminate data by way of
criteria. For example, I want to show the output where field 1 is not
null AND field 2 to does not equal a specific input AND field 3 does
not equal a specif input. I need the out put to show where only all 3
of these are met. What I am getting is an output showing where ANY of
these are met. Here is the line from the sql

WHERE ((Not (qrySE16_New.WBS) Is Null) AND ((qrySE16_New.MRPC)<>"TLS")
AND ((qrySE16_New.[Doc Type])<>"ID"));

Obviously I am doing it wrong. Please help. Thanks

This is sort of a "double negative" problem - it's NOT Not Equal to one or
more of the criteria. Try

WHERE Not (qrySE16_New.WBS Is Null AND qrySE16_New.MRPC="TLS" AND
qrySE16_New.[Doc Type]="ID");

so it collect all of the conditions that you want to exclude, and if theyare
all met, the NOT will reject that record.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Actually it works using OR in place of AND. It's counterintuitive, but
it works. I double checked. Thanks.

 

[Tony Toews]

Actually it works using OR in place of AND. It's counterintuitive, but
it works.

Welcome to Boolean logic. Part of a course on programmable logic
controllers I took at the local college while I as going to high
school. I'm still quite greatful for the concepts it taught me such
as how basic computer circuits are designed.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/