Grab Custom Fiscal Year Count Based on System Clocks date

[steve21nj]

How do you grab a custom Fiscal Year's values based on the system clock's date?

I am building a query where I want to see the number of closed cases based on the current custom fiscal year with the system clocks date. The report that it feeds only cares about the current FY.

I need the System Clock's FY value in this query

 SELECT shortname AS Station, NZ(TotalCount.TotalCases,0) AS [Cases Complete]
FROM StationList LEFT JOIN (SELECT station, count([Open Issues].ID) AS TotalCases FROM [Open Issues] WHERE [Status]="Closed" GROUP BY Station)  AS TotalCount ON StationList.shortname =TotalCount.station;

Within the Query Open Issues I have the FY broken up

fiscalYear: IIf(Month([Issues].[Opened Date])>=10,"FY" & Year(DateAdd("yyyy",1,[Issues].[Opened Date])),"FY" & Year([Issues].[Opened Date]))

Any suggestions?

 

[plog]

You should make a custom FiscalYear function, you pass it a date, it returns the FY that date falls in:

FY: get_FiscalYear([YourDateField])

That way, whenever you need it, you just call a function instead of hunting for your logic and pasting it everywhere. Then, to get today's fiscal year you do this:

TodayFY: get_FiscalYear(Date())

 

[jdraw]

This may be of some use re Fiscal Year

 

[steve21nj]

thank you for the best practice tips.

 

[Ranman256]

Instead of continually building code for the fiscal year in every query,
build a function, then you can just call the function in every query.

sql usage:
select [date], FiscalYr([date]) as FiscalYear from table

Public Function FiscalYr(ByVal pvDate)
Dim mo, day, yr

mo = Month(pvDate)
day = Mid(pvDate, 4, 2)
yr = Year(pvDate)

Select Case True
   Case mo = 12 And day > 14
      yr = yr + 1
      
   Case mo = 1 And day < 15
      yr = yr - 1
End Select
FiscalYr = yr
End Function