[AccessD] Query not returning desired results

[Paul Hartland]

I cant really see anything wrong with the query as such, I would remove all
the open and closing brackets though and clean it up a little but thats
just a personal approach, if you are getting results returned that include PAT
28-5A or PAT 56-6A then I would suggest that either the field '
Other_Pattern_as_Well_API' is blank and not null or the dates are differeng
in the linked allocations table

On Tue, 10 Dec 2019, 15:14 Kaup, Chester, <Chester_Kaup at kindermorgan.com>
wrote:

> Put another way what I want to do is exclude records where the field Other
> Pattern contains one of two values (all other values are acceptable) and
> the field Other_Pattern_as_Well_API is null for those Other Pattern 2
> values. Here is an example of a record that should be excluded. Hope that
> makes sense
>
> Well_In_CO2PAT  Well_in_CO2PAT_API      ProductionMonth Other_Pattern
>      Other_Pattern_as_Well   Other_Pattern_as_Well_API       3mo_rbi
> SumOf3mo_rbi    Factor_2
> 28-10                   42415305460000          11/1/2019
>  PAT 28-5A               28-5A
>              195497.44       1,691,433.78            0.12
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Stuart McLachlan
> Sent: Tuesday, December 10, 2019 9:01 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query not returning desired results
>
> [This email message was received from the Internet and came from outside
> of Kinder Morgan]
>
>
> You said: "where one field equals a certain value and another field is
> null"
> but that's not what your code does.
>
> It includes all values for Other_Pattern except two.
>
>
>
>
> On 10 Dec 2019 at 14:20, Kaup, Chester wrote:
>
> > Got rid of all the unneeded parentheses but still got the same
> > incorrect result. Thanks for the help.
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> > Of Stuart McLachlan Sent: Monday, December 09, 2019 2:54 PM To: Access
> > Developers discussion and problem solving Subject: Re: [AccessD] Query
> > not returning desired results
> >
> > [This email message was received from the Internet and came from
> > outside of Kinder Morgan]
> >
> >
> > hey are all AND conditions, so for a start, just get rid of all
> > parentheses.
> >
> >
> > On 9 Dec 2019 at 17:15, Kaup, Chester wrote:
> >
> > > I have a query in which I want to exclude records when one field
> > > equals a certain value and another field in the same record is null.
> > > So far it have been unsuccessful after several attempts of moving
> > > around parenthesis. Here is my statement. I defer to and appreciate
> > > the assistance of other members
> > >
> > > WHERE ((([tbl 3 Month Running Inj Data].Other_Pattern)<>"PAT 28-5A"
> > > And ([tbl 3 Month Running Inj Data].Other_Pattern)<>"PAT 56-6A") AND
> > > (([tbl 3 Month Running Inj Data].Other_Pattern_as_Well_API) Is Null)
> > > AND ((Allocations.Date)=#11/1/1948#))
> > >
> > > Also tried this
> > >
> > > WHERE (([tbl 3 Month Running Inj Data].Other_Pattern<>"PAT 28-5A"
> > > AND [tbl 3 Month Running Inj Data].Other_Pattern<>"PAT 56-6A" AND
> > > [tbl 3 Month Running Inj Data].Other_Pattern_as_Well_API Is Null)
> > > AND Allocations.Date=#11/1/1948#)
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>