Report question on grouping

[chadlmiller]

Ok so I have a grouping by date. Dates are grouping by Qrtr report shows Q4 2009 the code which shows this is as follows in the properties sheet in the control source:="Q" & Format$([Opened Date],"q yyyy")

OK so here is my question:
How do I modify the qtr report to show up for Government qtrs rather than calendar qtrs. example
The date of 10 Sept 2009 appears in Q3 2009 and 10 Oct 2009 appears in Q4 2009

I need it to show:
10 Sept 2009 appears in Q4 2009
and
10 Oct 2009 appears in Q1 2010

Thank You.

 

[chadlmiller]

Sorry

Access 2007
Windows XP

 

[lameid]

This is a really old function I wrote to return the fiscal year. Honestly it returns the begining year of a fiscal year.

It is a good place to strat to think about things conceptually.

Function FiscalYear(dtIn As Variant, intMonthStartFiscalYear As Integer, Optional intDayStartFiscalYear As Integer = 1) As Variant
      'Returns the 'Fiscal Year'
    'Where the Fiscal year is the year In which the fiscal year started
    'intMonthStartFiscalYear is the month in which the fiscal year starts
    'and intDayStartFiscalYear is the day of the month the fiscal year starts
    'Note Use Dateadd Function to add a year
    'for second calendar year of fiscal year time span
    On Error GoTo fiscalYear_err
    Dim lngYear As Long
    If IsNull(dtIn) Or IsNull(intMonthStartFiscalYear) Then
        Err.Raise vbObjectError + 1, "FiscalYear", "Input missing for Fiscal Year Function"
    End If
    If IsDate(dtIn) = False Then
        Err.Raise vbObjectError + 1, "FiscalYear", "Value passed to dtIn is not a date. dtIn = " & dtIn
    End If
        
    lngYear = Year(dtIn)
    If Month(dtIn) < intMonthStartFiscalYear Then
        lngYear = lngYear - 1
    End If
    If Month(dtIn) = intMonthStartFiscalYear Then
        If Day(dtIn) < intDayStartFiscalYear Then
            lngYear = lngYear - 1
        End If
    End If
    FiscalYear = lngYear
Exit Function
fiscalYear_err:
    FiscalYear = Null
    Debug.Print "Error " & Err.Number & ": " & Err.Description
End Function

 

[dhookom]

If you think this will never change, I would use:

="Q" & Format$(DateAdd("m",3,[Opened Date]),"q yyyy")

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

When you pair my code and duane's together, you can group by fiscal year and then quarter... Unless you really only want to run for one fiscal year at a time.