Jim Lawrence
accessd at shaw.ca
Tue Sep 18 13:57:42 CDT 2007
Hi Chester: Have you tried putting # signs around the date? #MyDate# That should tell Access that is to be processed. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Tuesday, September 18, 2007 11:34 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Query returns data for wrong time period If I take the source query and make it into a make table query and then run the attached query off the created table everything works fine. Apparently access does not think ProductionDate: CDate(Fix(dbo_V_S_Oil_Metering_Daily_Volumes!Date)) is a date until stored in a table. Thanks for the ideas, comments. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos Sent: Monday, September 17, 2007 1:17 PM To: Access Developers discussion and problem solving Subject: Re: [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 -- 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