Your function seems rather inconsistent. Firstly the date ranges overlap on
31 July/1 August; secondly it returns the same value for the two years 31
July 2008 to 1 August 2010. Did you mean to return 2010 in the last case?

For greater flexibility the following returns the accounting year in a format
such as 2009-10 by passing the date value, and the day and month when the
accounting year starts into the function:

Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As
Integer) As String

Dim dtmYearStart As Date

' get start of accounting year in year of date value
dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)

' if date value is before start of accounting year
' accounting year starts year previous to date's year,
' otherwise it starts with date's year
If DateVal dtmYearStart Then
AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")
Else
AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")
End If

End Function

For the UK fiscal year traditionally starting 5 April for instance:

AcctYear([TheDate], 4, 5)

In your case it would be:

AcctYear([TheDate], 8, 1)

I'm assuming your year starts 1 August.

To return just the ending year you'd amend it to:

If DateVal dtmYearStart Then
AcctYear = Year(DateVal)
Else
AcctYear = Year(DateVal) + 1
End If

and make the data type of the return value an integer number rather than a
string.

Ken Sheridan
Stafford, England

Bre-x wrote:
Public Function my_dates(the_date As Date) As Integer

Select Case the_date

Case #7/31/2005# To #8/1/2006#
my_dates = 2006
Case #7/31/2006# To #8/1/2007#
my_dates = 2007
Case #7/31/2007# To #8/1/2008#
my_dates = 2008
Case #7/31/2008# To #8/1/2009#
my_dates = 2009
Case #7/31/2009# To #8/1/2010#
my_dates = 2009
Case Else
my_date = 0
End Select
End Function

Thank you!!!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201004/1