Fiscal Year as Query Criteria

[BigNate]

Hello Everyone,

I created a new field in my query called FiscalYear, where I want the field to identify the fiscal year, where the fiscal year runs from April to March of the next year. I successfully have this field created. My expression for this calculation is as follows:

IIf(Month([ClaimInputTbl]![DateofComplaint])>3,Year([ClaimInputTbl]![DateofComplaint]),Year([ClaimInputTbl]![DateofComplaint]))

Here is where my problem is though--I want my query to only show records that are for the current fiscal year.

Can someone please tell me how to do that? Thanks!

 

[HiTechCoach]

Hello Everyone,

I created a new field in my query called FiscalYear, where I want the field to identify the fiscal year, where the fiscal year runs from April to March of the next year. I successfully have this field created. My expression for this calculation is as follows:

IIf(Month([ClaimInputTbl]![DateofComplaint])>3,Year([ClaimInputTbl]![DateofComplaint]),Year([ClaimInputTbl]![DateofComplaint]))

Here is where my problem is though--I want my query to only show records that are for the current fiscal year.

Can someone please tell me how to do that? Thanks!

I don't understand how your calculation for the fiscal year works:

IIf(Month([ClaimInputTbl]![DateofComplaint])>3,Year([ClaimInputTbl]![DateofComplaint]),Year([ClaimInputTbl]![DateofComplaint])) always return the same thing which is Year([ClaimInputTbl]![DateofComplaint]).


What I understand you to say is that your fiscal year goes from April to March. That would mean you are currently in FY2016 which from April 2015 through March 2016.


I like to use a User Defien Function to do this anywhere needed.

Place the following in a code module named modFiscalYear

Option Compare Database
Option Explicit


 Const FYMonthStart = 4   ' Numeric value representing the first month of the fiscal year.
 Const FYDayStart = 1     ' Numeric value representing the first day of the fiscal year.
 Const FYOffset = -1      ' 0 means the fiscal year starts in the current calendar year.
                          ' -1 means the fiscal year starts in the previous calendar year.


Public Function GetFiscalYear(ByVal pDateToCheck As Variant) As Variant

   If pDateToCheck < DateSerial(Year(pDateToCheck), FYMonthStart, FYDayStart) Then
      GetFiscalYear = Year(pDateToCheck) - FYOffset - 1
   Else
      GetFiscalYear = Year(pDateToCheck) - FYOffset
   End If
End Function

Public Function GetFiscalMonth(ByVal pDateToCheck As Variant) As Variant
  Dim FYMonth As Integer
  
  FYMonth = Month(pDateToCheck) - FYMonthStart + 1
  
  If Day(pDateToCheck) < FYDayStart Then FYMonth = FYMonth - 1
  
  If FYMonth < 1 Then FYMonth = FYMonth + 12
         
  GetFiscalMonth = FYMonth
End Function

Example usage:

? GetFiscalYear(#3/31/2015#) 
 2015 

? GetFiscalMonth(#3/31/2015#) 
 12 

? GetFiscalYear(#4/1/2015#) 
 2016 


? GetFiscalMonth(#4/1/2015#) 
 1

Here is where my problem is though--I want my query to only show records that are for the current fiscal year.

Using the functions above your query would look like below.

For example if you want to use the Fiscal year for the current date then:

Where GetFiscalYear([ClaimInputTbl].[DateofComplaint]) = GetFiscalYear(Date())

 

[BigNate]

Thanks for the explanation coach. What you explaine makes sense although me be a bit too complex for me considering my current skill set. After messing around with this for several hours, I solved this problem a different way. I found all scenarios where the date would be in the current fiscal year, and assigned that a value of 1. If not, I assigned a value of 0 in the field I created. I then dropped in the totals row and selected "Where" and set criteria to 1.

My formula is as follows (and it works):
IIf(Month([ClaimInputTbl]![DateofComplaint])<4 And Month(Now())<4 And Year([ClaimInputTbl]![DateofComplaint])=Year(Now()) Or Year([ClaimInputTbl]![DateofComplaint])=Year(Now())-1 And Month([ClaimInputTbl]![DateofComplaint])>3 And Month(Now())<4 Or Month(Now())>3 And Month([ClaimInputTbl]![DateofComplaint])>3 And Year([ClaimInputTbl]![DateofComplaint])=Year(Now()) Or Month(Now())>3 And Month([DateofComplaint])<4 And Year([DateofComplaint])-1=Year(Now()),1,0)

 

[HiTechCoach]

Thanks for the explanation coach. What you explaine makes sense although me be a bit too complex for me considering my current skill set. After messing around with this for several hours, I solved this problem a different way. I found all scenarios where the date would be in the current fiscal year, and assigned that a value of 1. If not, I assigned a value of 0 in the field I created. I then dropped in the totals row and selected "Where" and set criteria to 1.

My formula is as follows (and it works):
IIf(Month([ClaimInputTbl]![DateofComplaint])<4 And Month(Now())<4 And Year([ClaimInputTbl]![DateofComplaint])=Year(Now()) Or Year([ClaimInputTbl]![DateofComplaint])=Year(Now())-1 And Month([ClaimInputTbl]![DateofComplaint])>3 And Month(Now())<4 Or Month(Now())>3 And Month([ClaimInputTbl]![DateofComplaint])>3 And Year([ClaimInputTbl]![DateofComplaint])=Year(Now()) Or Month(Now())>3 And Month([DateofComplaint])<4 And Year([DateofComplaint])-1=Year(Now()),1,0)

That seams very complicated.
\
here is a much simpler way to get just teh the Fiscal Year:

FY: Year(DateSerial(Year([ClaimInputTbl]![DateofComplaint]), Month([ClaimInputTbl]![DateofComplaint]) + (12 - [ClaimInputTbl]![DateofComplaint]+ 1), 1))

To use the functions I posted previous you would:

1) create a new code module.
2) Paste in the code and save the module as modFiscalYear.
3) Now they are ready to use. No need to understand the code.

Just pass a data to the functions. Just like you would use Year() you would use GetFiscalYear() or Month() you would use GetFiscalMonth().

 

[HiTechCoach]

I was curios if you see if the IFF() actually worked. It does but gets really slow with lots of records compared to my functions.

I converted your IFF() to the in a function that can be used anywhere. I also made one change so the it returns a Boolean (True or False). Makng it a function did not change the performance at all. It is still slower when having to evaluate the huge IIF()

Public Function IsCurrentFY(pDate As Date) As Boolean

IsCurrentFY = IIf(Month(pDate) < 4 And Month(Now()) < 4 And Year(pDate) = Year(Now()) Or Year(pDate) = Year(Now()) - 1 And Month(pDate) > 3 And Month(Now()) < 4 Or Month(Now()) > 3 And Month(pDate) > 3 And Year(pDate) = Year(Now()) Or Month(Now()) > 3 And Month(pDate) < 4 And Year(pDate) - 1 = Year(Now()), True, False)

End Function

Again this would go in a VBA Code module.

Your query would look like:

Where IsCurrentFY([ClaimInputTbl]![DateofComplaint]) = True

Note: You code will only work for the current fiscal year based on the PC's clock. The functions I posted will work for any fiscal year you need.

TIP: Be sure to test with data that spans multiple fiscal years.

 

[BigNate]

Nice! Thanks for the info. To be honest, I didn't know you could save a module and reference it like that. I have this code saved and it will definitely come in handy. Thanks again!

 

[mathematician]

Hello Everyone,

I created a new field in my query called FiscalYear, where I want the field to identify the fiscal year, where the fiscal year runs from April to March of the next year. I successfully have this field created. My expression for this calculation is as follows:

IIf(Month([ClaimInputTbl]![DateofComplaint])>3,Year([ClaimInputTbl]![DateofComplaint]),Year([ClaimInputTbl]![DateofComplaint]))

Here is where my problem is though--I want my query to only show records that are for the current fiscal year.

Can someone please tell me how to do that? Thanks!

Remove the fiscal year field and just filter your query by including [dateofComplaint] table field in the SELECT statement and in the WHERE clause type any of the following criteria

>=#01/04/2015# And <=#31/03/2016#
OR
Between #01/04/2015# And #31/03/2016#

 

[HiTechCoach]

Remove the fiscal year field and just filter your query by including [dateofComplaint] table field in the SELECT statement and in the WHERE clause type any of the following criteria

>=#01/04/2015# And <=#31/03/2016#
OR
Between #01/04/2015# And #31/03/2016#

Why would you want to hard code this when the fiscal year changes ever year?

 

[BigNate]

Yeah, that's the true challenge of this original task at hand. The code needs to work for any day going forward and needs to first identify what fiscal year the analysis is being done in.

Thanks again everyone.

 

[HiTechCoach]

Yeah, that's the true challenge of this original task at hand. The code needs to work for any day going forward and needs to first identify what fiscal year the analysis is being done in.

Thanks again everyone.

Yes. That is exactly what my original functions do. You use my functions GetFiscalYear() or Get FiscalMonth() just like you would Year() or Month() except they return the Fiscal Year or Month.


This:

[ClaimInputTbl]![DateofComplaint] >=#01/04/2015# And <=#31/03/2016#

OR

[ClaimInputTbl]![DateofComplaint] Between #01/04/2015# And #31/03/2016#

can be dynamic using my function like this:

GetFiscalYear([ClaimInputTbl]![DateofComplaint]) = GetFiscalYear(Date())

I only posted a few of the Fiscal Year functions I use. I also have several for getting the start and end dates of the Fiscal year and more. I have to deal with this in my accounting system for clients that have fiscal years that are not the calendar year.