Function fgetFiscalMonth(ByVal dtDate As Date, Optional iMonthStart As Integer = 1, _
Optional iDayStart As Integer = 1)
'http://www.utteraccess.com/forum/index.php?showtopic=1374556&st=0
'20160308
'============================== NOTES ============================
' code derived from Microsoft Knowledge Base article 132101
' generalized so as not to require declared constants
'
'Inputs:
'
'-----------------------------------------------------------------------------------
Const cstrProcedure = "fgetFiscalMonth"
Dim iMonth As Integer
On Error GoTo HandleError
iMonth = Month(dtDate) - iMonthStart + 1
If Day(dtDate) < iDayStart Then
iMonth = iMonth - 1
End If
If iMonth < 1 Then
iMonth = iMonth + 12
End If
fgetFiscalMonth = iMonth
HandleExit:
Exit Function
HandleError:
'ErrorHandle Err, Erl(), cstrModule & "." & cstrProcedure
Resume HandleExit
End Function
Public Function fGetFiscalQuarter(vdtDate As Date, Optional vintMonthStart As Integer = 1) As Integer
'http://www.utteraccess.com/forum/index.php?showtopic=1374556&st=0
'20160308
' Procedure: fGetFiscalQuarter
' DateTime: 10/03/2007 11:05:34 AM
' Author: Glenn Lloyd
' Description: determine to which quarter of a fiscal year a specified month belongs
'--
Const cstrProcedure = "fGetFiscalQuarter"
On Error GoTo HandleError
Select Case fgetFiscalMonth(vdtDate, vintMonthStart)
Case 1, 2, 3
fGetFiscalQuarter = 1
Case 4, 5, 6
fGetFiscalQuarter = 2
Case 7, 8, 9
fGetFiscalQuarter = 3
Case 10, 11, 12
fGetFiscalQuarter = 4
End Select
HandleExit:
Exit Function
HandleError:
' ErrorHandle Err, Erl(), cstrModule & "." & cstrProcedure
Resume HandleExit
End Function
Public Function FiscalYear(rDate As Date) As String
'Modified from original function to just show year
'JezWalters Post
'https://www.experts-exchange.com/questions/26931063/Microsoft-Access-function-return-financial-year-based-on-a-date-value.html
'20180309
If Month(rDate) < 5 Or _
(Month(rDate) = 5 And _
Day(rDate) < 1) Then
FiscalYear = Year(rDate) - 1
Else
FiscalYear = Year(rDate)
End If
End Function
Function GetPreviousQuarter(qtrDate As Date)
'Define quarter based on date, however quarter is
'previous quarter as assumption being that the
'rundate from NF will be after the end of the quarter
'so if Q3 is 11/01 - 01/31 and the NF query is run on 02/20
'we are actually running for the date range of Q3 and
'therefore want to record the quarter as Q3 rather than Q4
'20180220
' Q1 05/01 - 07/31
' Q2 08/01 - 10/31
' Q3 11/01 - 01/31
' Q4 02/01 - 04/30
Select Case Month(qtrDate)
Case 5, 6, 7
GetPreviousQuarter = "Q4"
Case 8, 9, 10
GetPreviousQuarter = "Q1"
Case 11, 12, 1
GetPreviousQuarter = "Q2"
Case 2, 3, 4
GetPreviousQuarter = "Q3"
End Select
End Function
Function GetPreviousQuarterEnd(qtrDate As Date)
'Define quarter based on date, however quarter is
'previous quarter as assumption being that the
'rundate from NF will be after the end of the quarter
'so if Q3 is 11/01 - 01/31 and the NF query is run on 02/20
'we are actually running for the date range of Q3 and
'therefore want to record the quarter as Q3 rather than Q4
'20180220
' Q1 05/01 - 07/31
' Q2 08/01 - 10/31
' Q3 11/01 - 01/31
' Q4 02/01 - 04/30
Dim stQuarterEnd As String
Select Case Month(qtrDate)
Case 5, 6, 7
stQuarterEnd = "04/30/" & Year(Date) 'Q4
Case 8, 9, 10
stQuarterEnd = "07/31/" & Year(Date) 'Q1
Case 11, 12, 1
stQuarterEnd = "10/31/" & Year(Date) 'Q2
Case 2, 3, 4
stQuarterEnd = "01/31/" & Year(Date) 'Q3
End Select
If CDate(stQuarterEnd) > qtrDate Then
GetPreviousQuarterEnd = DateAdd("yyyy", -1, CDate(stQuarterEnd))
Else
GetPreviousQuarterEnd = CDate(stQuarterEnd)
End If
End Function