How do I change the default year setting from Jan-Dec to Oct-Sep

[JessicaS]

I am trying to analyze the data I have to find our product sales for the last
several years but our fiscal year is from Oct-Sept and the data base is
calculating the year as Jan-Dec. How can I change the year calculation?
Thanks!

 

[Michel Walsh]

Hi,


This kind of problem is generally solved by adding -9 months to the real
date, before taking the YEAR part:


YEAR( DateAdd( "m", -9, realDateTime ) )



so that a date between First of October 2005, and 30th September 2006 will
return 2005 for the given expression.

Alternatively, add + 3 months, to get 2006 rather than 2005, with the
previous range.


Hoping it makes sense,
Vanderghast, Access MVP

 

[David Lloyd]

One approach is to create a new field in your query called, for instance,
FiscalYear, as follows:

FiscalYear: IIF(Month([SalesDate])>=10, Year([SalesDate])+1,
Year([SalesDate]))

You can then constrain this field for the particular year(s) you desire.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to analyze the data I have to find our product sales for the
last
several years but our fiscal year is from Oct-Sept and the data base is
calculating the year as Jan-Dec. How can I change the year calculation?
Thanks!

 

[JessicaS]

Hello,

Thanks for the help. This makes perfect sense to me but I can't seem to get
it to work I keep getting a pop up that says data type mismatch in the
criteria expression. Any ideas?

Thanks!

 

[Michel Walsh]

Hi,


Is the date a constant in your query? I mean, if you typed a fixed,
hard coded date, you should use #, as in #31-12-2004#, not 31-12-2004
which is two subtractions, not a constant (hard coded) date. You don't use
# around a name that contains a date, just around a physical hard-coded
constant date.

If it is a parameter for a full date, try

CDate( myDateParameter )

rather than just the name of the parameter. That would force Access to
recognize the parameter value (by default, a variant, so, anything) to be a
date, rather than a string, or whatever it may be mislead to imagine what
the parameter represent. Also, to make all chances on your side, be sure you
use the date parts as in your locale regional settings: mm-dd-yyyy for US,
dd-mm-yyyy in almost all other English countries, yyyy-mm-dd for most
other European countries. Last note, if you use # delimiter, you should
preferably use US sequence, irrelevantly to your regional setting.




Hoping it may help,
Vanderghast, Access MVP

 

[Cyberwolf]

Try this

DatePart("yyyy",DateAdd("m",-4,[YourDate]))