Figuring fiscal year based on formatted date

[Guest]

Our fiscal year starts 7/1. Currently it is fiscal year 2005, but on 7/1 it
will be fiscal year 2006. I am trying to write a simple function that will
figure out what fiscal year we are in. I have my current function attached to
many queries and forms to automatically set the year for my users.

I have noticed a flaw in my code. It seems to work right for all months
except 10, 11 and 12 (Oct-Dec). I think I am missing something easy here,
either with what I have or via another way to do this calculation.

Any help would be greatly appreciated.


Public Function FigureBudgetYear() As String

If Format(Date, "m/d") > "6/30" Then
FigureBudgetYear = Format(Date, "yyyy") + 1
Else
FigureBudgetYear = Format(Date, "yyyy")
End If

End Function


Thanks in advance,
Clint

 

[Douglas J. Steele]

You need to use 2 digit months:

If Format(Date, "mm/dd") > "06/30" Then

 

[David Lloyd]

Clint:

I believe the reason your current function does not work for months 10-12 is
that you are doing a string comparison in your IF statement. When comparing
strings, it compares the first character of each string, in this case "6"
versus "1" (as in 10, 11, 12) and determines that 6 is greater than 1.

One alternative approach would be something like the following.

Public Function FigureBudgetYear() As String
Dim iYear As Integer

If Month(Date) <= 6 Then
iYear = Year(Date)
Else
iYear = Year(Date) + 1
End If

FigureBudgetYear = CStr(iYear)

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Our fiscal year starts 7/1. Currently it is fiscal year 2005, but on 7/1 it
will be fiscal year 2006. I am trying to write a simple function that will
figure out what fiscal year we are in. I have my current function attached
to
many queries and forms to automatically set the year for my users.

I have noticed a flaw in my code. It seems to work right for all months
except 10, 11 and 12 (Oct-Dec). I think I am missing something easy here,
either with what I have or via another way to do this calculation.

Any help would be greatly appreciated.


Public Function FigureBudgetYear() As String

If Format(Date, "m/d") > "6/30" Then
FigureBudgetYear = Format(Date, "yyyy") + 1
Else
FigureBudgetYear = Format(Date, "yyyy")
End If

End Function


Thanks in advance,
Clint