Prev: Opening Forms based on Combo Box Selection (Access 2007)
Next: How do I prevent others from editing my Access 2007 database?
From: Rothman on 16 Feb 2010 14:32
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.
From: John Spencer on 16 Feb 2010 16:00
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

Rothman wrote:
> 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.
 | 
Pages: 1
Prev: Opening Forms based on Combo Box Selection (Access 2007)
Next: How do I prevent others from editing my Access 2007 database?