Fiscal Period

[Natalie]

I am trying to automate fiscal period in a query. I want the criteria to look
up the current system date [date()] and classify that date into a fiscal
period (which I have a table (calendar) to show which dates go into which
fiscal period. Our calendar is based on 4-4-5 week.)
Ex:
February = Period 1 (Jan 30, 2006 - Feb 26, 2006)
July = Period 6 (Jun 26, 2006 - July 30, 2006)
So if I am running the report on Jun 27, 2006 - I need the criteria in the
query to look up the system date and realize that that date is for period 6
not period 5 (Jun) automatically.

 

[Michel Walsh]

Hi,


Let

n= DateDiff("ww", Now, #30-1-2006#)


then, the period should be given by:


3*(n-1)\13 + Choose( 1+ (n-1 MOD 13), 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3,
3, 3)



I suspect the formula would fail for 2007, or for year with 53 weeks, or for
watever reasons. If so, change the constant for the new frist week of the
first period (instead of, here, #30-1-2006#).


Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

What is the structure and contents of your Calendar table?

Does it have two fields? PeriodName and PeriodStartDate (one record for each
period)

Does it have two fields? PeriodName and PeriodDate (one entry for each date
in the period) - so it has records like
Period 6 : Jun 26, 2006
Period 6 : Jun 27, 2006

Does it have three fields? PeriodName, PeriodStartDate, PeriodEndDate (one
record for each period)

There are solutions for all three of these, but they vary. Also, it would
help if you posted the query that you are using. (View: SQL from the
menubar).