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