Financial Year Query

[Molden]

Hi All,

Really struggling with this.

I am trying to get the current financial year figures vs last year financial year (up until the current month) in an access query

So this month is January so:

Current Financial Year = Apr 2014 - Jan 2015
Previous Financial Year = Apr 2013 - Jan 2014

and ideally next month it would include the latest figures so Apr 14 - Feb 15 and Apr 13 - Feb 15

The field I want to sum is called Volume
And the Date I am trying to look at is called DelvDate

Any help would be great.

Thanks in advance

 

[Molden]

For anyone else who has a similar problem and wants to spare the heart ache and tears of doing it themselves please feel free to use the below:

PreviousYear: Sum(IIf(Month(Date())>=4,IIf([DelvDate]>=DateSerial(Year(Now())-1,4,1) And [DelvDate]<DateSerial(Year(Now()),Month(Now())+1,1),[Volume],0),IIf([DelvDate]>=DateSerial(Year(Now())-2,4,1) And [DelvDate]<DateSerial(Year(Now())-1,Month(Now())+1,1),[Volume],0)))



CurrentYear: Sum(IIf(Month(Date())>=4,IIf([DelvDate]>=DateSerial(Year(Now()),4,1) And [DelvDate]<=DateSerial(Year(Now())+1,3,31),[Volume],0),IIf([DelvDate]>=DateSerial(Year(Now())-1,4,1) And [DelvDate]<=DateSerial(Year(Now()),3,31),[Volume],0)))