I have been trying to figure this out with no luck. I just want to return records that have a date within the current fiscal year - whatever the current fiscal year is. Ours starts 7/1 and ends 6/30. I am doing this in VBA so when the user selects the "YTD(year to date)" button, the records for the current fiscal year are retrieved. I found these functions, but am having no luck putting the whole thing together. Has anyone done this?
I have tried a lot of variations of this but records ending in 2011 are still being returned:[CODE]Month(GetFiscalMonth([End Date]) > 6 and Year([End Date] between Year(Date())-1 and Year(Date())
Thanks for any assistance you can provide!
FUNCTIONS:
[CODE]Option Explicit
Const FMonthStart = 7 ' Numeric value representing the first month
' of the fiscal year.
Const FDayStart = 1 ' Numeric value representing the first day of
' the fiscal year.
Const FYearOffset = -1 ' 0 means the fiscal year starts in the
' current calendar year.
' -1 means the fiscal year starts in the
' previous calendar year.
Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function
Function GetFiscalMonth(ByVal x As Variant)
Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function
/CODE]