Year To Date Sums on Filtered Report

[dainamae]

Hello,

I am trying to create a report that shows Weekly sums, as well as YTD sums based on data entered by the User.

Month Shift TotalHours
Jan A 15

summary YTD 15

Jan B 15

summary YTD 15


Feb A 15

summary YTD 30

Feb B 30

summary YTD 45

Above you will find a generic report of what I am trying to get. I have this working if I use Overall Running sums in the Group footers, however, if the user only wants Feb's data, then the YTD sums calculate only data showing on the report (Feb sums). I currently use a query that calculates the Monthly summary information for the backend of my report and then use VBA code to allow for filtering. Any suggestions?

 

[JerryDennison]

You'll need to DSum() to get the YTD totals. The syntax is exactly like DLookUp(). So you can limit it to the ending date passed to the underlying query. So for example it would look something like:

=DSum("FieldName", "TableName", "[DateField] Between #01/01/" & Year(Now()) & "# AND " & [EndingDate])