eliminating criteria in a query

  • Thread starter Thread starter pat67
  • Start date Start date
P

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

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
 
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
Click to expand...
WHERE ((Not (qrySE16_New.WBS) Is Null) AND ((qrySE16_New.MRPC)<>"TLS")
AND ((qrySE16_New.[Doc Type])<>"ID"));
Click to expand...
Obviously I am doing it wrong. Please help. Thanks
Click to expand...

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

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

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/
 
Back
Top