[AccessD] Query returns data for wrong time period

Kaup, Chester Chester_Kaup at kindermorgan.com
Wed Sep 19 16:31:20 CDT 2007


Here is a sample of the result of your query. Thanks for all the effort
you put into looking at this. I don't like the reserved word Date used
in the SQL server view. May be a problem. One again thinks for your
efforts. I may get the DBA to change the field name in SQL server.

Date	tDtdot	tDtApost	tdtcdatedot	tdtfixdot
tDtcdatefixdot	tDtcfixbrac	tDtcfixjdt	tDtcfixfncdt
5/16/2002 11:00:00 PM	5/16/2002 11:00:00 PM	5/16/2002 11:00:00 PM
5/16/2002 11:00:00 PM	37392	5/16/2002	5/16/2002
5/16/2002	9/19/2007
5/17/2002 11:00:00 PM	5/17/2002 11:00:00 PM	5/17/2002 11:00:00 PM
5/17/2002 11:00:00 PM	37393	5/17/2002	5/17/2002
5/17/2002	9/19/2007
5/18/2002 11:00:00 PM	5/18/2002 11:00:00 PM	5/18/2002 11:00:00 PM
5/18/2002 11:00:00 PM	37394	5/18/2002	5/18/2002
5/18/2002	9/19/2007
5/19/2002 11:00:00 PM	5/19/2002 11:00:00 PM	5/19/2002 11:00:00 PM
5/19/2002 11:00:00 PM	37395	5/19/2002	5/19/2002
5/19/2002	9/19/2007
5/20/2002 11:00:00 PM	5/20/2002 11:00:00 PM	5/20/2002 11:00:00 PM
5/20/2002 11:00:00 PM	37396	5/20/2002	5/20/2002
5/20/2002	9/19/2007

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

Well I have run the adjustment of a full date several different ways and
get the following

CREATE_DT	                   tDtBrac
tDtFixbrac	tDtCdfixBrac	tDtCFixdot	tDtdtfnc
tdtapost
08/27/2001 11:32:38 AM  08/27/2001 11:32:38 AM	37130	08/27/2001
08/27/2001	09/19/2007	08/27/2001 11:32:38 AM
08/27/2001 02:03:53 PM  08/27/2001 02:03:53 PM	37130	08/27/2001
08/27/2001	09/19/2007	08/27/2001 02:03:53 PM
08/29/2001 10:51:42 AM  08/29/2001 10:51:42 AM	37132	08/29/2001
08/29/2001	09/19/2007	08/29/2001 10:51:42 AM
11/20/2001 04:55:00 PM  11/20/2001 04:55:00 PM	37215	11/20/2001
11/20/2001	09/19/2007	11/20/2001 04:55:00 PM
11/20/2001 04:59:56 PM  11/20/2001 04:59:56 PM	37215	11/20/2001
11/20/2001	09/19/2007	11/20/2001 04:59:56 PM
11/27/2001 03:51:16 PM  11/27/2001 03:51:16 PM	37222	11/27/2001
11/27/2001	09/19/2007	11/27/2001 03:51:16 PM
03/15/2002 02:59:11 PM  03/15/2002 02:59:11 PM	37330	03/15/2002
03/15/2002	09/19/2007	03/15/2002 02:59:11 PM
03/18/2002 03:32:46 PM  03/18/2002 03:32:46 PM	37333	03/18/2002
03/18/2002	09/19/2007	03/18/2002 03:32:46 PM
10/17/2002 03:13:46 PM  10/17/2002 03:13:46 PM	37546	10/17/2002
10/17/2002	09/19/2007	10/17/2002 03:13:46 PM
01/12/2004 01:22:06 PM  01/12/2004 01:22:06 PM	37998	01/12/2004
01/12/2004	09/19/2007	01/12/2004 01:22:06 PM
01/12/2004 01:23:37 PM  01/12/2004 01:23:37 PM	37998	01/12/2004
01/12/2004	09/19/2007	01/12/2004 01:23:37 PM
05/19/2004 01:46:16 PM  05/19/2004 01:46:16 PM	38126	05/19/2004
05/19/2004	09/19/2007	05/19/2004 01:46:16 PM

I did run things against a view here and had no problem.  It could be
the way the view is created that is causing the problem.  Can't tell you
much more without seeing the table/view desc and the the sql for that.
By any chance is the date in a binary index?

You could try this and see what comes back and maybe that would help.  
SELECT  dbo_V_S_Oil_Metering_Daily_Volumes.Date as tDtdot,
        dbo_V_S_Oil_Metering_Daily_Volumes!Date as tDtApost,
        cdate(dbo_V_S_Oil_Metering_Daily_Volumes.Date) as tdtcdatedot,
        fix(dbo_V_S_Oil_Metering_Daily_Volumes.Date) as tdtfixdot,
        Cdate(Fix(dbo_V_S_Oil_Metering_Daily_Volumes.Date)) as
tDtcdatefixdot,
        cdate(fix([dbo_V_S_Oil_Metering_Daily_Volumes].[date])) as
tDtcfixbrac,
        cdate(fix([date])) as tDtcfixjdt,
        cdate(fix(date())) as tDtcfixfncdt
>From dbo_V_S_Oil_Metering_Daily_Volumes
Order by dbo_V_S_Oil_Metering_Daily_Volumes.Date ascending

Sorry wish I could be more help

**************************************************
* Patricia O'Connor
* Associate Computer Programmer Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at nysemail.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. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Kaup, Chester
> Sent: Wednesday, September 19, 2007 03:30 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Query returns data for wrong time period
> 
> This is a regular access query.
>


-- 
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