Finacial years

[Mr C]

Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

[Mark]

See if this helps --

From my files - see below my SIG
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Get The Fiscal Year Or Fiscal Month Of A Particular Date By Using An Expression

To get the fiscal year or fiscal month for a OrderDate where the fiscal year
begins June 16 use the following expressions:

Fyear = Year([OrderDate])-IIf([OrderDate]<
DateSerial(Year([OrderDate]),6,16),1,0)

Fmonth = (Month([OrderDate])+IIf(Day([OrderDate])<16,6,7)-1) Mod 12+1 NOTE: The
FYear and FMonth expressions can be used for for any beginning date of a Fiscal
year by modifying the Date field, the day and the month, to the appropriate
values for the fiscal year. For example, if the fiscal year begins on 9/15 of
the current calendar year, you can modify the previous expressions as follows:

Fyear = Year([FieldName])-IIf([FieldName]<
DateSerial(Year([FieldName]),9,15),1,0)

FMonth: (Month([FieldName])+IIf(Day([FieldName])<15,9,10)-1) Mod 12+1
If the fiscal year begins on 9/15 of the previous calendar year, you can modify
the FYear expression as follows:

Fyear = Year([FieldName])-IIf([FieldName

"Mr C" <ma*********@hotmail.com> wrote in message
news:70*************************@posting.google.co m...

Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

[Dave O]

I was able to get a working scenario together with the DateDiff()
function. However, you'll need to determine how your records will
handle projects longer than one year that span 3 fiscal years, for
instance. It's do-able, but requires some additional planning.

ma*********@hotmail.com (Mr C) wrote in message news:<70*************************@posting.google.c om>...

Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

[Dean Covey]

The way I have tackled these situations is as follows:

In one field of my table, I would create a field as:
DateSerial(Year(Date()),Month(Date())+1,1)-1

This will set the date to the last day of the month. For this example,
I am calling this field "Period". If course you can have this update by an
update query or the example above uses today's date.
I would create another field Year. Use an update query as follows:
IIf(Month([Period])>8,Year([Period])+1,Year([Period])). If 8 (August) is
the last month of the fiscal year. Otherwise, change the formula to suite
your needs.
Hope this helps.

--
Dean Covey
www.coveyaccounting.com

MS-Office Certified:
http://www.microsoft.com/learning/mc...st/default.asp

"Mr C" <ma*********@hotmail.com> wrote in message
news:70*************************@posting.google.co m...

Hi

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

[Mike Sherrill]

dOn 8 Apr 2004 02:53:27 -0700, ma*********@hotmail.com (Mr C) wrote:

If i have a field in a database start date and end date, how am I able
to find out which financial years the two dates cover, and populate
colunms in a database with how many months in each financial year ie
if start date was 1/1/2004 to 1/1/2005 there would be 4 months in the
financial year 03/04 and 8 months in the financial year 04/05

Use a table.

--
Mike Sherrill
Information Management Systems