Full Version: Running Sum Not Working
M2C
Hi
I am trying to extend a runnng sum in a query to a daily sum rather than a month. I have been basing my efforts on this http://support.microsoft.com/kb/290136 without success. My query has 4 fields
Field1
AYear: DatePart("yyyy",[RaceMeetDt])

Field2
AMonth: DatePart("m",[RaceMeetDt])

Field3

Field4
RunTot: DSum("RacePay","Race","DatePart('d',[RaceMeetDt])<=" & [ADay] & " And DatePart('m',[RaceMeetDt])<=" & [AMonth] & " And DatePart('yyyy',[RaceMeetDt])<=" & [AYear] & "")

Race is the tbl
RaceMeetDt is the date field I want to sum on
RacePay is the field I want to sum on a daily basis. There are many numbers in a day that I want to sum each day e.g day1 has 3+2+4 = 9 and day2 has 2+2+6=10 so I want Field4 to say 9 first and then 19 etc.

If take out the ADay part in the query its fine but add it in it does not sum correctly

Any ideas?
Thanks
Chris
theDBguy
Hi Chris,

Just curious... What do you get if you use something like this?

RunTot: DSum("RacePay", "Race", "RaceMeetDt<=#" & Format([RaceMeetDt], "yyyy-mm-dd") & "#")

Just my 2 cents...
projecttoday
You cannot compare dates that way. If you comparison is based on May 10, then February 11 thru 28 would be rejected.
Doug Steele
Seems unnecessarily complicated to me.

What happens if you eliminate the three calculations for AYear, AMonth and ADay and simply use:

RunTot: DSum("RacePay","Race","[RaceMeetDt]<=" & Format([RaceMeetDt], "#yyyy\-mm\-dd\#"))

M2C
Bootiful! Very many thanks

Cheers
Chris

theDBguy
Hi Chris,

Robert, Doug, and I are all happy to help. Good luck with your project.
M2C
Gentlemen many thanks to you all
Chris