Fiscal Year Query

[depawl]

My company's fiscal year begins on April 1st. I need to design a query that pulls data from the most recent 3 full fiscal years. For example, today the query would need to pull data from April 1, 2012 through March 31, 2015. A year from today, the query would need to pull data from April 1, 2013 through March 31, 2016. I've tried using the DartPart and DateAdd functions but so far am stumped.
Thanks in advance.

 

[plog]

You should make a function in a module for this. My experience is that fiscal year calculation is needed in many places of a database, so instead of jamming the logic everywhere it is needed, you simply make a function, then call it wherever you need it (Form, Query, Report).

You would pass it a date and it would return an integer to designate the fiscal year that date is in. The logic would simply be made up of the Year (http://www.techonthenet.com/access/functions/date/year.php) and Month (http://www.techonthenet.com/access/functions/date/month.php) functions.

 

[arnelgp]

SELECT * FROM yourTable WHERE yourDateField Between DateSerial(Year(Date())-2,4,1) And DateSerial(Year(Date())+1, 3, 31)

 

[Galaxiom]

You should make a function in a module for this. My experience is that fiscal year calculation is needed in many places of a database, so instead of jamming the logic everywhere it is needed, you simply make a function, then call it wherever you need it (Form, Query, Report)..

While it is certainly useful to have such a function, it needs to be used carefully. Querying by FY function means that the date on every record in the table would need to have the function applied.

The FY function is custom in VBA so it cannot be translated to SQL. Hence the database engine cannot process it. Consequently every record would need to be returned before the select could be applied.

To avoid this delay, always preselect the required data using the where clause posted by arnelgp. This allows the engine to use the index on the field to select records without reading them. On a big table it makes a huge difference to the time required to run the query.

 

[depawl]

Thanks all, I will give this a try at work today.

 

[MarkK]

Combining the ideas in this thread, write a function that returns a valid WHERE clause expression. It'll need a signature like . . .

Function GetFiscalYearWhereClause(d1 as date, dateFieldName as string) as string

This gives you Galaxiom's speed, and plog's flexibility, by customizing arnel's SQL.

 

[jdraw]

Here is a M$oft link re Fiscal year, fiscal month and calculations.

 

[depawl]

Well this appears to be a bit more challenging than originally anticipated. Since I need the query to return the most recent 3 full fiscal years of data, for example, on March 31, 2015 the query would need to return data from April, 1 2011 to March 31, 2014; whereas on April 1, 2015, the query would need to return data from April 1, 2012 to March 31, 2015. Furthermore the logic of using DateSerial(Year(Date()) does not appear to work. That function (I think) would return a different value when the calendar year changes (but the fiscal year has not changed). I'm sure there must be an easy way around this, but currently I'm stuck.

 

[Galaxiom]

Something in this style:

SELECT * FROM yourtable WHERE datefield Between DateSerial(Year(Date())-(iif(Month(date())>3,2,3)),4,1) And DateSerial(Year(Date())+(iif(Month(date())>3,1,0)), 3, 31)

I have not checked the results. Adjust the True and False parameters of the IIF accordingly if the answer is not quite what you want.

 

[plog]

Using my method, which I still advocate this is your query:

SELECT * 
FROM YourTable
WHERE get_FiscalYear([YourDateField])>=(get_FiscalYear(Date())-3)

Then the get_FiscalYear psuedo code looks like this:

get_FiscalYear(in_Date) 

FY=Year(in_Date)
if Month(in_Date)> ~Fiscal Year Begin Month Here~ Then FY = FY + 1

return FY

End Function

 

[Galaxiom]

Using my method, which I still advocate this is your query:

SELECT * 
FROM YourTable
WHERE get_FiscalYear([YourDateField])>=(get_FiscalYear(Date())-3)

You need to get your head around why that isn't a good solution for the query. It is a fundamental query design choice that separates amateurs from professionals.

Using a function as you suggest requires every record to be returned to Access for processing and can easily be one hundred times slower than selecting with a date range using the field's index.

It won't be obvious for small numbers of records but your query will slow to a crawl as the number of records increase. It will also put a vastly bigger load on the database engine.

You have sacrificed processing efficiency for simplicity of expression. If you want to keep it simple to write then use MarkK's variation.

 

[MarkK]

Here's a cFiscalYear class I have. It's lying around doing nothing. I tweaked it to roll over on April 1, and I added a method to return a WHERE clause expression . . .

Option Compare Database
Option Explicit

Private m_date As Date
Private m_start
Private m_end

[COLOR="Green"]'********************************************************************
'*                                                        Sep 12 2012
'*       Properties>
'*
'********************************************************************[/COLOR]
Property Get FiscalYear() As Long
    FiscalYear = Year(Me.DateEnd)
End Property

Property Get DateStart() As Date
    If IsEmpty(m_start) Then Calculate
    DateStart = m_start
End Property

Property Get DateEnd() As Date
    If IsEmpty(m_end) Then Calculate
    DateEnd = m_end
End Property

[COLOR="Green"]'********************************************************************
'*                                                        Sep 12 2012
'*       Methods>
'*
'********************************************************************[/COLOR]
Private Sub Class_Initialize()
    m_date = Date
End Sub

Function LoadByYear(Year As Long) As cFiscalYear
    Refresh
    Set LoadByYear = Me.LoadByDate(DateSerial(Year, 1, 1))
End Function

Function LoadByDate(d1 As Date) As cFiscalYear
    Refresh
    m_date = DateValue(d1)   [COLOR="Green"] 'make sure there is no time component[/COLOR]
    Set LoadByDate = Me
End Function

Sub Refresh()
    m_start = Empty
    m_end = Empty
End Sub

Function GetWhereClauseExpression(DateFieldName As String) As String
    GetWhereClauseExpression = _
        "( " & _
        DateFieldName & " >= #" & Me.DateStart & "# AND " & _
        DateFieldName & " <= #" & Me.DateEnd & "# " & _
        ") "
End Function

Function ToString() As String
    ToString = _
        "Fiscal Year: " & Me.FiscalYear & vbCrLf & _
        "Date Start.: " & Me.DateStart & vbCrLf & _
        "Date End...: " & Me.DateEnd & vbCrLf & _
        "WHERE......: " & Me.GetWhereClauseExpression("MyDate")
End Function

[COLOR="Green"]'********************************************************************
'*                                                        Sep 12 2012
'*       Utils>
'*
'********************************************************************[/COLOR]
Private Sub Calculate()
    If m_date = 0 Then Err.Raise 5, "cFiscalYear.Calculate()"
    
    If Month(m_date) > 3 Then
        m_start = DateSerial(Year(m_date), 4, 1)
        m_end = DateSerial(Year(m_date) + 1, 3, 31)
    Else
        m_start = DateSerial(Year(m_date) - 1, 4, 1)
        m_end = DateSerial(Year(m_date), 3, 31)
    End If
End Sub

You can test it with a little routine like . . .

Private Sub Test17419023487()
    Dim tmp As New cFiscalYear
    tmp.LoadByDate #1/1/2014#
    Debug.Print tmp.ToString
End Sub

You can modify this to return three year chunks or whatever. I wrote it because I have a customer who's fiscal year end date has changed a few times over the years, so the Calculate method for them was pretty complex, but it's a good example of a class, and has a few different ways to Initialize it, and once you have it it's so easy to use, and so on . . .

 

[depawl]

Many thanks to all, I'm sure they are all workable solutions, but with my (very) limited programming skills, Galaxiom's code is the only one that I have any idea of where to start.
Galaxiom: Can you explain the logic behind the
iif(Month(date())>3,2,3
portion of your code?

 

[jdraw]

depawl,

You have to look at the functions within Galaxiom's sample.

He uses Dateserial() which happens to include an IIF()
The links will take you to additional info and examples.

 .....DateSerial(Year(Date())-(iif(Month(date())>3,2,3)),4,1).....

 

[depawl]

jdraw:
Yes, I am somewhat familiar with both of those functions, but was stumped by Galaxiom's use of the IIF Month within the DateSerial year, but your reply prompted me to chew on it a bit more, and i think it's beginning to sink in.
Thanks.

 

[Galaxiom]

Can you explain the logic behind the
iif(Month(date())>3,2,3
portion of your code?

If the month of the current date is greater than 3 (ie March) then go back two years, otherwise go back three years.

 

[depawl]

Got it. Thanks very much.