Dsum Cumulative Issue - by years

[bass_twitch]

Hi all

Firstly, thanks for all your help on my last issue,
I've got another one that I am trying to solve now.

Ok so at this moment in time I am using a dsum forumla to calculate cumulative totals based on both individual projects and time in some of my queries. And this is working well. However it continues to be cumulative over the FYs,

Ultimately I would like it to reset the cumulative count every financial year, any suggestions?

This is my current formula:

RECCumVal: FormatCurrency(DSum("[PlannedCashflowRecurrent]","qry_ProjectMonthlyPVCalc","[ProjectID]=" & [ProjID] & " And [Mth_Yr]<=" & [MthYr] & ""))

Is there any way of tweaking the And [Mth_Yr]<=" & [MthYr] & to be for individual FYs, or can I add a third criteria to the formula?

Also FYI, an example of a MthYr value is:
20110.01 where 2011 is the year and 0.01 is July.

TIA.

 

[bass_twitch]

No ideas?

 

[bass_twitch]

bumpity bump?

 

[bass_twitch]

I guess I have stumped you all

 

[vbaInet]

You really should be doing this in a report.

Is there any way of tweaking the And [Mth_Yr]<=" & [MthYr] & to be for individual FYs, or can I add a third criteria to the formula?

Yes, add third criteria to the forumula for the year part.

 

[bass_twitch]

Thanks, simple and I don't know why I didn't think of that *facepalms*
It is now working with this:
CumVal: FormatCurrency(DSum("[PV-PlannedTotalExpenditure]","qry_ProjectMonthlyPVCalc","[ProjectID]=" & [ProjID] & " And [Mth_Yr]<=" & [MthYr] & " And [Yr]=" & [BaselineYear] & ""))

Also it can't be done in a report (i.e. on the fly), as I need to be able to manipulate the data further (and pump it into graphs that are in reports). I did consider doing it that way but decided that a make tbl qry was the best bet

 

[vbaInet]

Glad you got it working.