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