[AccessD] Query returns data for wrong time period

Kaup, Chester Chester_Kaup at kindermorgan.com
Mon Sep 17 12:47:46 CDT 2007


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





More information about the AccessD mailing list