[AccessD] FW: DateDiff problem in a query

Kaup, Chester Chester_Kaup at kindermorgan.com
Thu Sep 8 11:20:50 CDT 2005


Tried your idea in the date DateDiff part of the query instead of
DateSerial with the same erroneous results

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Elam, Debbie
Sent: Thursday, September 08, 2005 10:59 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: DateDiff problem in a query

You can use months in Date functions.  Try DateAdd("m", 1, Date) to get
1
month from the Date.

Debbie


  _____  

From: Kaup, Chester 
Sent: Thursday, September 08, 2005 10:39 AM
To: 'Access Developers discussion and problem solving'
Subject: DateDiff problem in a query

 

I have the following query to calculate a moving total. The theory is to
calculate a total of a given date and the 2 prior dates. For example the
row of the query with a date of 5/1/2005 should have a total of vm_wtri
for March, April and May. I am using the DateSerial and DateDiff
functions to go back 2 months. As you can see in the results some months
this works and some it does not. The number of days back appears to be
correct. What am I missing? Thanks for the assistance.

 

SELECT [tbl One Well Water Injection].PID, [tbl One Well Water
Injection].Date, [tbl One Well Water Injection].vm_wtri, (Select
Sum(vm_wtri) From [tbl One Well Water Injection] AS T1 Where T1.Date >=
[tbl One Well Water Injection].Date
-DateDiff("d",DateSerial(Year([Date]),Month([Date])-2,Day([Date])),[Date
])  And T1.Date <=   [tbl One Well Water Injection].Date) AS
MovingTotal, DateSerial(Year([Date]),Month([Date])-2,Day([Date])) AS [2
Months Prior],
DateDiff("d",DateSerial(Year([Date]),Month([Date])-2,Day([Date])),[Date]
) AS [Days Diff]

FROM [tbl One Well Water Injection]

WHERE ((([tbl One Well Water Injection].Date)>=#1/1/1994#))

ORDER BY [tbl One Well Water Injection].Date;

 

The results:

 

PID                   Date                 vm_wtri
MovingTotal       2 Months Prior   Days Diff

424150174300   9/1/2004
7/1/2004            62

424150174300   10/1/2004
8/1/2004            61

424150174300   11/1/2004          23546.37           23546.37
9/1/2004            61

424150174300   12/1/2004          61430.49           84976.86
10/1/2004          61

424150174300   1/1/2005            43755.91           128732.77
11/1/2004          61

424150174300   2/1/2005            45906.27           89662.18
12/1/2004          62

424150174300   3/1/2005            34813.93           124476.11
1/1/2005            59

424150174300   4/1/2005            44469.81           125190.01
2/1/2005            59

424150174300   5/1/2005            3881.54               48351.35
3/1/2005            61

424150174300   6/1/2005                                       3881.54
4/1/2005            61

424150174300   7/1/2005                                       3881.54
5/1/2005            61

424150174300   8/1/2005
6/1/2005            61

 

 

Chester Kaup

Engineering Technician

Kinder Morgan CO2 Company, LLP

Office (432) 688-3797

FAX (432) 688-3799

 

 

No trees were killed in the sending of this message. However a large
number of electrons were terribly inconvenienced.

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
- JENKENS & GILCHRIST E-MAIL NOTICE - This transmission may be: (1)
subject
to the Attorney-Client Privilege, (2) an attorney work product, or (3)
strictly confidential. If you are not the intended recipient of this
message, you may not disclose, print, copy or disseminate this
information.
If you have received this in error, please reply and notify the sender
(only) and delete the message. Unauthorized interception of this e-mail
is a
violation of federal criminal law. 
This communication does not reflect an intention by the sender or the
sender's client or principal to conduct a transaction or make any
agreement
by electronic means.  Nothing contained in this message or in any
attachment
shall satisfy the requirements for a writing, and nothing contained
herein
shall constitute a contract or electronic signature under the Electronic
Signatures in Global and National Commerce Act, any version of the
Uniform
Electronic Transactions Act or any other statute governing electronic
transactions.
-- 
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