Current Year vs Pevious Year - Fixed months

[SamDemon]

I have created a company incident database which records staff holidays, sickness (against rolling 12 month Bradford score) and lateness.

The problem I have is that I have been asked to display staff sickness based on our financial year of October to September. What is the best method in order to display information based on current financial year?

i.e.

Todays date - 04/01/12 would require information between 01/10/11 and 30/09/12

Date = 30/12/11 would also require information between 01/10/11 and 30/09/12

Date = 01/11/12 would require information between 01/10/12 and 30/09/13

Thanks

Sam

It's just common sense, shame sense isn't common!

 

[SamDemon]

Between DateSerial(IIf(Month(Date())>10, Year(Date()), Year(Date())-1), 9, 1) And DateSerial(IIf(Month(Date())>10, Year(Date())+1, Year(Date())), 9, 30)

It's just common sense, shame sense isn't common!

 

[SamDemon]

Should have been - Between DateSerial(IIf(Month(Date())>10,Year(Date()),Year(Date())-1),10,1) And DateSerial(IIf(Month(Date())>10,Year(Date())+1,Year(Date())),9,30)

It's just common sense, shame sense isn't common!