Last Financial Years Data? 1

[MikeAuz1979]

Hi,

Using access 2000 I'm kinda stumped on how to restrict daily information to just last financial years data. (Assuming an Australian fin year of 1-July to 30-Jun)

My table looks like:

Date Site Amount
01-jan-00 1 1001
02-jan-00 1 987
01-jan-00 2 7899
etc.

Right now I just have a hardcoded criteria of Between #1 Jul 2006# And #30 Jun 2007# on the Date field, but does anyone else have any ideas how this can be acheieved dynamically so this query will continue to show last financial years data even in future years?

Thanks for any help
Mike

 

[TomCologne]

Try something like this:

Between DateSerial(Year(Date())-1,7,1) And DateSerial(Year(Date()),6,30)

TomCologne

 

[cheerio]

Mike I hope you do not have a column called Date as it's a reserved word and likely to cause problems such as conflict with the Access function Date(). For the purpose of this reply I will name it FinDate.

Tom your solution assumed the calendar year and financial year are the same. This will be true until 31 December. From 1st January you will be returning the current financial year and not the previous one.

Try this modified version of your code:

WHERE (
MONTH(DATE()) > 6 AND FinDate Between DateSerial(Year(Date())-1,7,1) And DateSerial(Year(Date()),6,30)
)
OR
(
MONTH(DATE()) < 7 AND FinDate Between DateSerial(Year(Date())-2,7,1) And DateSerial(Year(Date())-1,6,30)
)

Alternatively you could try using an IF to test the current month:

Between DateSerial(Year(Date())-(iif(MONTH(DATE()>6,1,2),7,1) And DateSerial(Year(Date())())-(iif(MONTH(DATE()>6,0,1),6,30)

 

[MikeAuz1979]

Thanks Cherio, great work. I had to tweak some brackets but it tested okay for various dates.

Between DateSerial(Year(Date())-IIf(Month(Date())>6,1,2),7,1) And DateSerial(Year(Date())-IIf(Month(Date())>6,0,1),6,30)

Mike

 

[cheerio]

Mike

Thanks for the star. I am glad I was able to help.

Cheerio