[AccessD] Query returns data for wrong time period

So the data in the date being checked is not stored in a date format?
Is that correct?  I am wondering if you need to do a conversion on the
input form field to get it into the same frame of reference. Also I am
wondering if the fieldname is actually called DATE?  That would
possibly be a problem even with the qualification in front of it. Date
is possibly a reserved word and you might be getting something you
don't intend.

Just tossing out some ideas for you to maybe look into or think about.

GK

On 9/17/07, Kaup, Chester <Chester_Kaup at kindermorgan.com> wrote:
> The query returns a start date before the one entered. It appears to be
> the first record most of the time. The first record is 5/16/2002. If I
> hard code the date in the query works fine. The production date in the
> source query has the following formula
> ProductionDate: CDate(Fix(dbo_V_S_Oil_Metering_Daily_Volumes!Date))
>
> Example of query result
> Start Date Entered      Start Date Returned
> 6/20/2005                       6/3/2002
> 1/1/2005                        5/16/2002
> 3/5/2006                        5/16/2002
> 1/1/2007                        5/16/2002
> 10/11/2004                      5/16/2002
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
> Sent: Monday, September 17, 2007 12:14 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query returns data for wrong time period
>
> I think you need to describe a little more exactly what the
> discrepancy is from what you enter to what data is selected. Are you
> getting too much data or not enough?  You are taking into
> consideration that date type fields also have TIME included in them
> that will sometimes affect selection?  Usually with start dates that
> doesn't catch you as much as with end dates. Also are there possibly
> date format issues involved?  Perhaps are your dates stored in a
> different time zone or something so that they are really not the value
> you think they are?
>
> Can you give us some samples of what you entered verses what was
> selected and not selected as you expected?
>
> GK
>
> On 9/17/07, Kaup, Chester <Chester_Kaup at kindermorgan.com> wrote:
> > When I run the following query the start date of the results is not
> the same as the start date from the form. The end date works correctly.
> I have checked the value of StartDate and it appears correct.
> Suggestions?
> >
> > SELECT [qry All Manifolds Oil Gas Water].PID, [qry All Manifolds Oil
> Gas Water].ProductionDate, [qry All Manifolds Oil Gas Water].Oil, [qry
> All Manifolds Oil Gas Water].Water, [qry All Manifolds Oil Gas
> Water].Gas, [qry All Manifolds Oil Gas Water].GOR
> > FROM [qry All Manifolds Oil Gas Water]
> > WHERE ((([qry All Manifolds Oil Gas Water].PID)=[Forms]![frm Select
> Manifold]![ManifoldList]) AND (([qry All Manifolds Oil Gas
> Water].ProductionDate)>=[Forms]![frm Time Interval]![StartDate] And
> ([qry All Manifolds Oil Gas Water].ProductionDate)<=[Forms]![frm Time
> Interval]![EndDate]))
> > ORDER BY [qry All Manifolds Oil Gas Water].ProductionDate;
> >
> > Chester Kaup
> > Engineering Technician
> > Kinder Morgan CO2 Company, LLP
> > Office (432) 688-3797
> > FAX (432) 688-3799
>
> --
> Gary Kjos
> garykjos at gmail.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
>


-- 
Gary Kjos
garykjos at gmail.com