Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Developer Toolkits / January 2005

Tip: Looking for answers? Try searching our database.

Business Day Calculator

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Karen Hart - 29 Jan 2005 02:32 GMT
I found a wonderful solution to counting business days including holidays at
www.dbforums.com/t981086.html.  The solution is to create a table storing
all calendar dates, indicating which are business days and holidays, like
this:

Calendar table:
caldate        wkday   holiday
2004-02-01   1
2004-02-02   2
2004-02-03   3
2004-02-04   4
2004-02-05   5
2004-02-06   6
2004-02-07   7
2004-02-08   1
2004-02-09   2
2004-02-10   3
2004-02-11   4
2004-02-12   5
2004-02-13   6
2004-02-14   7
2004-02-15   1
2004-02-16   2   President's Day
2004-02-17   3

Then create a query like this:

select count (*)
from Calendar
where caldate between current_date and 'yourdate'
and wkday in (2,3,4,5,6)
and holiday is null

It works perfectly! And being that I sweat bullets when I look at
complicated code, it is perfect for me...
But I'd like to query it in a slightly different way...in my company, we
need to know what is 5 business days from today.  So in the above example,
if today was 2004-02-09, 5 business days in the future would return the
result 2004-02-17.

Can anyone help?  You could be my new personal hero!

Signed,
L.A. Airhead :-)
Douglas J. Steele - 29 Jan 2005 13:11 GMT
Sorry, but I think you're going to have to bite the bullet and use VBA code.

http://www.mvps.org/access/datetime/date0012.htm at "The Access Web" is a
very complete example, courtesy of Ken Getz. If you're having problems
understanding the code (and, to be honest, it really isn't necessary that
you understand how each routine works, just that you know when to call which
routine), you might take a look at "Chapter 2: Working with Dates and Times"
at http://www.developershandbook.com/Articles.htm

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I found a wonderful solution to counting business days including holidays
>at www.dbforums.com/t981086.html.  The solution is to create a table
[quoted text clipped - 40 lines]
> Signed,
> L.A. Airhead :-)
 
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.