[AccessD] FW: DateDiff problem in a query

  _____  

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

 

 

 

  _____  

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.