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
ADay: DatePart("d",[RaceMeetDt])

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

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

You cannot compare dates that way. If you comparison is based on May 10, then February 11 thru 28 would be rejected.

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\#"))

Bootiful! Very many thanks

Cheers
Chris

Hi Chris,



Robert, Doug, and I are all happy to help. Good luck with your project.

Gentlemen many thanks to you all
Chris