Fiscal Yr Chg- FirstWeekOfYear

[Sue]

I got one answer but it does not help to just add months
using datepart to the "m". I can come up with a field
easy that give me the quarter (1,2,3,4) but the problem
becomes expressing the year. Example: October 1,2004 is
Qrt1-2005. If I just use a field expressing 1,2,3,4 I
will never get 10/1/04 = 1 to fall in 2005 where it
should. Had anyone used FirstWeekOfYear which is
supposed to be used to change how MS see "q" quarters?
Need more help. Thanks. Sue

 

[John Vinson]

I got one answer but it does not help to just add months
using datepart to the "m". I can come up with a field
easy that give me the quarter (1,2,3,4) but the problem
becomes expressing the year. Example: October 1,2004 is
Qrt1-2005. If I just use a field expressing 1,2,3,4 I
will never get 10/1/04 = 1 to fall in 2005 where it
should. Had anyone used FirstWeekOfYear which is
supposed to be used to change how MS see "q" quarters?
Need more help. Thanks. Sue

To my knowledge, FirstWeekOfYear has nothing to do with quarters, only
with weeks.

If October 1 is the start of FY 2005, then you can add three months to
any date to determine which fiscal year it's in. Say:

FiscalYear: Year(DateAdd("m", 3, [datefield])
FiscalQuarter: DatePart("q", DateAdd("m", 3, [datefield])


John W. Vinson[MVP]

 

[Sue]

Thanks, that works. I just didn't think to do the year
the same as the qtr change. MS Access Help suggest using
the FirstWeekof Year to change the "q" but then doesn't
explain how. Thanks for your help.

-----Original Message-----

I got one answer but it does not help to just add months
using datepart to the "m". I can come up with a field
easy that give me the quarter (1,2,3,4) but the problem
becomes expressing the year. Example: October 1,2004 is
Qrt1-2005. If I just use a field expressing 1,2,3,4 I
will never get 10/1/04 = 1 to fall in 2005 where it
should. Had anyone used FirstWeekOfYear which is
supposed to be used to change how MS see "q" quarters?
Need more help. Thanks. Sue

To my knowledge, FirstWeekOfYear has nothing to do with quarters, only
with weeks.

If October 1 is the start of FY 2005, then you can add three months to
any date to determine which fiscal year it's in. Say:

FiscalYear: Year(DateAdd("m", 3, [datefield])
FiscalQuarter: DatePart("q", DateAdd("m", 3, [datefield])


John W. Vinson[MVP]
.