Fiscal Six-Month Periods

[Rothman]

I have a database with a bunch of dates in it. I'd like to create a new
calculated field that designates the associated date in a six-month period
through a query.

The periods go from April 1st to September 30th (1st half of our fiscal
year) and then from October 1st to March 31st (2nd half of our fiscal year).

So, a date in OBLG_DATE of 6/1/09 would be designated in the new field
SFY_SIX as something like 2009S1. A date of 11/13/10 would be designated as
something like 2010S2.

Thanks for your help to this quite novice user in advance.

 

[John Spencer]

This expression should calculate the Fiscal year.
Year(DateAdd("m",-3,OBLG_Date)
This expression should calculate the period
IIF(Month(OBLG_Date) Between 4 and 9,"S1","S2")

Combine the two when you need to get the period label
Year(DateAdd("m",-3,OBLG_Date) & IIF(Month(OBLG_Date) Between 4 and
9,"S1","S2")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County