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!

 

[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!

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

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.

I thought He/She just want to get the result for the present FY. Adapt the following in your expression. I've tested and it works like a charm

Fiscal Year: IIf([DateofComplaint]>=DateSerial(Year([DateofComplaint]),4,1) And [DateofComplaint]<=DateSerial(Year([DateofComplaint])+1,3,31),"FY " & Year([DateofComplaint]) & "-" & (Year([DateofComplaint])+1),"FY " & (Year([DateofComplaint])-1) & "-" & (Year([DateofComplaint])))

 

[BigNate]

Understood, I was just reiterating what you were saying in post #8. It is a huge skill to have the ability to do what you do. I have a ton of Excel code saved for date extraction and stuff like this, and it comes in handy all the time.