Changing the Quarter year to a fiscal year (1 Viewer)

mandyj

New member
Local time
Today, 06:33
Joined
Feb 14, 2019
Messages
6
Hi!

I used the Query Wizard (I know that's cheating) to create a query that sorts the Timesheet data Quarterly.

The code is as follows;

Week Ending By Quarter: (Format$([Hours].[Week Ending],'\Qq yyyy')

However it returns Q1 as Jan - March and I want to change it to the Fiscal year so Q1 is Apr - Jun. Can anybody help me with some code to be able to do that please?

TIA
Mandy
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 02:33
Joined
Oct 17, 2012
Messages
3,276
Try this:

Format$(DateAdd("q", 1, [Hours].[WeekEnding]), '\Qq yyyy')

Edit: Accidentally left out the backslash.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 06:33
Joined
Sep 21, 2011
Messages
14,048
Try this:

Format$(DateAdd("q", 1, [Hours].[WeekEnding]), 'Qq yyyy')

Froth,

I get
? Format(DateAdd("q", 1, date()), "Qq yyyy")
22 2019 ?

Using your logic though

? Format$(DateAdd("m", -3, #07/19/2019#), "Q yyyy")
2 2019

produces the correct result I believe?
 
Last edited:

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 02:33
Joined
Oct 17, 2012
Messages
3,276
Damn, I left the backslash out. Updating my original post.
 

mandyj

New member
Local time
Today, 06:33
Joined
Feb 14, 2019
Messages
6
Try this:

Format$(DateAdd("q", 1, [Hours].[WeekEnding]), '\Qq yyyy')

Edit: Accidentally left out the backslash.


It has done something, but its not quite what I need. Thanks for your help though
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 02:33
Joined
Oct 17, 2012
Messages
3,276
You know, I think you're right. It should be -1, not +1.
 

sxschech

Registered User.
Local time
Yesterday, 23:33
Joined
Mar 2, 2010
Messages
791
Here are a bunch of fiscal functions found and put together for the previous place I worked because they used 01-MAY as start of fiscal year. Should be adaptable to other months with some edits depending on the function. Some won't need editing. However, if the solution already provided in previous posts of this thread works, then this would be overdone.

Code:
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
 

mandyj

New member
Local time
Today, 06:33
Joined
Feb 14, 2019
Messages
6
Thanks everyone, apologies for not replying sooner, I have been off for 10 days :-D

I'll give some of these a go now - fingers crossed
 

mandyj

New member
Local time
Today, 06:33
Joined
Feb 14, 2019
Messages
6
Success! Thanks Gasman and FrothingSlosh - heroes!

Thanks Also, sxschech, I'll no doubt be needing some of that code later.

Awesome, all of you x
 

Users who are viewing this thread

Top Bottom