I've watched your techhelp video on Fiscal Year. works great. IIF([CalendarMonth]<7,[CalendarYear]-1,[CalendarYear]). My question is how do I select with Criteria for a specific FY year?
Kevin Yip
@Reply 4 years ago
Hi William, you could make this easier by writing a custom function with VBA. I don't know which one of Richard's courses covers that topic.
Write a public function that looks like below. It returns the fiscal year for a specified date and a specified starting month for a fiscal year:
Function GetFiscalYear(d As Date, startmonth As Integer) As Integer
GetFiscalYear = IIf(Month(d) < startmonth, Year(d) - 1, Year(d))
End Function
Then you can create queries that use this function like any other functions:
SELECT MyDate FROM MyTable WHERE GetFiscalYear(MyDate, 7)=2019;
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
TechHelp.
