[AccessD] Query returns data for wrong time period

Kaup, Chester Chester_Kaup at kindermorgan.com
Wed Sep 19 07:57:20 CDT 2007


See responses below:

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of O'Connor,
Patricia (OTDA)
Sent: Tuesday, September 18, 2007 7:10 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query returns data for wrong time period

Are you saying if you use a hardcoded date in ProductionDate  example
ProductionDate: #08/18/2007# the query works fine? 
Correct

When you use  ProductionDate:
CDate(Fix(dbo_V_S_Oil_Metering_Daily_Volumes!Date)) it gets wacky?
Correct

Where is dbo_V_S_Oil_Metering_Daily_Volumes!Date located in a table or
form? 
It is a view in SQL server
 
How is it described? 
It is a DateTime data type

Why are you using fix?
To strip of the time part of the SQL server DateTime field
 
Just some quick tries. -- 
I rarely use ! for table fields in queries and I do queries using access
tables and oracle tables.
If  dbo_V_S_Oil_Metering_Daily_Volumes!Date is in a table then
Try  -   ProductionDate: Cdate(dbo_V_S_Oil_Metering_Daily_Volumes.Date)
      or   cdate([dbo_V_S_Oil_Metering_Daily_Volumes].[Date])
 
If this query is a regular access gui query you could try setting the
format to date in the gui
 
If dbo_V_S_Oil_Metering_Daily_Volumes!Date is in a form, you could set a
hidden field to a date format and insert the entered data into the
hidden field and use that in the query instead of have to do
Cdate(dbo_V_S_Oil_Metering_Daily_Volumes.Date) in the query.
 
BTW -  Date is usually considered a reserved word in most languages and
software. It can cause some strange happenings. 
Agreed however I did not create nor can I create the SQL server view

Thanks for the ideas.
 
 
*************************************************************
* Patricia E. O'Connor
* Associate Computer Programmer/Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (W) mailto:aa1160 at otda.state.ny.us
***********************************************************


--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged
or otherwise legally protected. It is intended only for the addressee.
If you received this e-mail in error or from someone who was not
authorized to send it to you, do not disseminate, copy or otherwise use
this e-mail or its attachments.  Please notify the sender immediately by
reply e-mail and delete the e-mail from your system. 


________________________________


From: accessd-bounces at databaseadvisors.com on behalf of Kaup, Chester
Sent: Mon 09/17/2007 1:47 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query returns data for wrong time period



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
<http://www.databaseadvisors.com/>  


-- 
AccessD mailing list 
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com
<http://www.databaseadvisors.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