Access 2010 Financial/Fiscal Year

[PWD]

Good afternoon, I have the following Query and ideally I'd like to be able to incorporate a further column whereby I was able to determine the Financial/Fiscal Year, i.e. April-March. Currently this has involved exporting to Excel (2010) and using a rather convoluted formula, which will only get more complex with the passing of time, to do this:

=IF(ISBLANK('2WW'!$A4),"",
IF(OR(
AND(YEAR('2WW'!$A4)=2017,MONTH('2WW'!$A4)>3),
AND(YEAR('2WW'!$A4)=2018,MONTH('2WW'!$A4)<4)),"2017-18",
IF(OR(
AND(YEAR('2WW'!$A4)=2018,MONTH('2WW'!$A4)>3),
AND(YEAR('2WW'!$A4)=2019,MONTH('2WW'!$A4)<4)),"2018-19",
IF(OR(
AND(YEAR('2WW'!$A4)=2019,MONTH('2WW'!$A4)>3),
AND(YEAR('2WW'!$A4)=2020,MONTH('2WW'!$A4)<4)),"2019-20",
""))))

qry_Seen_Months_2019_10_08

SELECT dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE AS FirstAppt
, IIf(IsNull([N2_9_FIRST_SEEN_DATE]),fNoApptReason([dbo_tblMAIN_REFERRALS]![L_NO_APP]),[N2_9_FIRST_SEEN_DATE]-[N2_6_RECEIPT_DATE]-[N2_14_ADJ_TIME]) AS [1st Wait]

FROM (dbo_tblMAIN_REFERRALS LEFT JOIN dbo_tblDEFINITIVE_TREATMENT ON dbo_tblMAIN_REFERRALS.CARE_ID = dbo_tblDEFINITIVE_TREATMENT.CARE_ID) INNER JOIN dbo_tblDEMOGRAPHICS ON dbo_tblMAIN_REFERRALS.PATIENT_ID = dbo_tblDEMOGRAPHICS.PATIENT_ID

WHERE (((dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE) Between #4/1/2017# And #10/31/2019#) AND ((dbo_tblMAIN_REFERRALS.N2_4_PRIORITY_TYPE)="03"))

ORDER BY dbo_tblMAIN_REFERRALS.N2_9_FIRST_SEEN_DATE;

Many thanks,
D€$

 

[Andrzejek]

Are you asking: how "to determine the Financial/Fiscal Year, i.e. April-March" for any given date?

Assuming Financial/Fiscal Year:
2020 is from April 1, 2019 to March 31, 2020
2021 is from April 1, 2020 to March 31, 2021
etc. ?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Create and maintain a financial calendar.

If you work for any company that has an IT org, I'd venture that they maintain such a calendar.

It might contain
[tt]
Calendar Date
Financial Year
Financial Month (some financial months don't begin on the first)
Day of Financial Month
Day of Financial year
Holiday (T/F)
Work Day of Year (some companies use a WorkDay value that increments without regard to year)(on Holiday, the WorkDay does not increment)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

What about creating helper table with three columns: [Col 1] - first day of fiscal year, [Col 2] - last day of fiscal year, and [Col 3]- notation for fiscal year (as "2017-18", "2018-19" etc. with proper dates for limits). Next, adding this table to a query without joining, picking [Col 3] with Between [Col 1] and [Col 2] for [N2_9_FIRST_SEEN_DATE] condition.

combo

 

[dhookom]

I would create a simple user-defined function that returns the number of months to add to the date to return the appropriate year. For example:

Public Function FiscalYear(datDate as Date) as Integer
    Dim intMthsToAdd as Integer
    intMthsToAdd = 3  [COLOR=#4E9A06]'add 3 months to the date to calculate Fiscal Year[/color][COLOR=#4E9A06][/color]
    FiscalYear = Year(DateAdd("m", intMthsToAdd, datDate))
End Function

This small function allows you to return the Fiscal Year based on any date. You can change the fiscal year definition by changing the intMthsToAdd. A query will also remain updateable which might not be the case if you use a small lookup table.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016