Change the start of the year

[Chrisopia]

It's frustrating me, and I've been on google for hours and still no closer.

I need to work out sums of departments and group by year, but the year falls between 01/08 - 31/07 and not 01/01 - 31/12.

I know how to work it out by year, but I'm trying to make it dynamic, so as more data enters the database it works it out accordingly without having to patch up the query every new year.

I hope this is possible... I just reached the end of the third year, so I should see 3 rows of totals when I'm done, 1 for each Financial year.

 

[MStef]

Try to use a PSEUDO years (31.07.2008 - 01.08.2009 is year 1
31.07.2009 - 01.08.2010 is year 2, and s.o.). I think you need
query and function (in module) for this.

 

[plog]

Try this:

FinancialYear: Year([YourDateField]) + iif(Month([YourDateField])>=8,1,0)

Here's what it will return for various dates:

1/1/2009 - 2009
7/31/2009 - 2009
8/1/2009 - 2010
12/31/2009 - 2010

 

[Chrisopia]

Thanks Plogg, that's exactly what I needed - worked a treat!!!