asked on

Microsoft Access function return financial year based on a date value

I am using Microsoft Access 2000 and I wish to write a function to return a financial year based on a date value.
The financial year should be computed as follows:
Let rdate be the date (e.g. receipt date)
Let fyear be the financial year

If the rdate ranges from

1-Apr-2005 to 31-Mar-2006
then fyear should be 20052006

1-Apr-2006 to 31-Mar-2007
then fyear should be 20062007

1-Apr-2007 to 31-Mar-2008
then fyear should be 20072008

1-Apr-2008 to 31-Mar-2009
then fyear should be 20082009

1-Apr-2009 to 31-Mar-2010
then fyear should be 20092010

1-Apr-2010 to 31-Mar-2011
then fyear should be 20102011

1-Apr-2011 to 31-Mar-2012
then fyear should be 20112012

and so on, let us say for the next 50years
I do not have data prior to 1-Apr-2005

cyberkiwi

function GetFinYear(dt as Date) as String
If Month(dt) >= 4 then
GetFinYear = (Year(dt)-1) & Year(dt)
else
GetFinYear = Year(dt) & (Year(dt)+1)
end if
end function

SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

In a query, you can use this expression

fyear: Iif(Month(rdate) >= 4, Year(rdate) & (Year(rdate) + 1), (Year(rdate) - 1) & Year(rdate))

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jez Walters

Are you sure you've got the date right for the start of each financial year - here in the UK each new financial year begins on April 6th (not April 1st)?

http://en.wikipedia.org/wiki/Fiscal_year

Jez Walters

In case you're interested, you can determine the United Kingdom financial year like this:

Public Function fyear(rDate As Date) As String

    If Month(rDate) < 4 Or _
       (Month(rDate) = 4 And _
        Day(rDate) < 6) Then
        fyear = Year(rDate) - 1
    Else
        fyear = Year(rDate)
    End If
    fyear = fyear & fyear + 1

End Function

Open in new window

GRayL

In Canada, it goes 1Apr to 31Mar - actually you can start on any month, but it is always the first of the start month to the 31st of the previous month next year.

Srinivas Mantha

ASKER

Thanks for the prompt support that provoked solutions for some other countries as well

Gustav Brock

You are welcome!

/gustav