help with fiscal year

[Guest]

my fiscal year starts from 01 October to the 30st September. I have two text
boxes, startdate and enddate. if i had 01 october 2005 in my start date and
30 september 2006 in my end date. is there a way to get vba code to increase
the years automatically when the new fiscal year starts?

i would want the user to type into the text boxes the dates, they cant be
fixed in vba code.

 

[Michel Walsh]

Hi,



Why not adding 3 months, with DateAdd, to the stored date, and read the
year( ) part of the result you so just got? Doing so, that operation will
give you the wanted computed YEAR value, for fiscal purposes, and otherwise,
in other cases, use the unmodified date stored in the table. Criteria and
GROUP BY also work on computed expression, so, again, that could solve SUMs
grouped by fiscal year, and so on.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

thanks for the response,

could you please try and explain in a bit more detail as im still trying to
get to grips with access.

 

[Michel Walsh]

Hi,


You keep the date as it is for most every day work, but WHEN you need
the fiscal year value itself, you use something like


YEAR( DATEADD( "m" , 3, yourDateOrYouFieldNameHere ) )


So, for any date between October 2005 and September 2006, that expression
will return 2006,
for any date between October 2006 and September 2007, it will return 2007,
and so on.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Many thanks

Hi,


You keep the date as it is for most every day work, but WHEN you need
the fiscal year value itself, you use something like


YEAR( DATEADD( "m" , 3, yourDateOrYouFieldNameHere ) )


So, for any date between October 2005 and September 2006, that expression
will return 2006,
for any date between October 2006 and September 2007, it will return 2007,
and so on.



Hoping it may help,
Vanderghast, Access MVP