 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2006Fiscal Period
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. 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 >I am trying to automate fiscal period in a query. I want the criteria to >look [quoted text clipped - 8 lines] > 6 > not period 5 (Jun) automatically. 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). >I am trying to automate fiscal period in a query. I want the criteria to >look [quoted text clipped - 8 lines] > 6 > not period 5 (Jun) automatically. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.