simple expression or SQL script to add business days to a date

  • Thread starter Thread starter tim.hambly
  • Start date Start date
T

tim.hambly

Hello, I have a question. I have been searching for an answer online for the
answer with no luck. Is there a simple SQL script or query expression I can
use to calculate business days from a date the user inputs? I have found some
different posts on how to add business days but I beleive it is showing me
VBA scripting for a form which I am not quite understanding as I keep getting
an error message whenever I try. Ideally what I want to achieve is there is a
button the user clicks on and it runs this query which asks for a date. The
user puts in the date and then the query will ask them for the interval (just
for example the user says date of 1/1/09 and inputs 10 for the interval. I
want the query to display 1/13/09 as this is 10 business days (Monday-Friday)
from 1/1/09. Can anyone help? I have already figured out the expression that
will allow me to do this with just regular days and that expression is:

Expr1: DateAdd("d",[Enter Interval],[Enter Date])
 
The DateAdd function is quite complex, but the code is in the VBA dll. I
worked for sometime to get this function to work properly but it has served
me well for more than 10 years now:

http://www.datastrat.com/Code/GetBusinessDay.txt

Once you've saved it as a module, you can call it very simply:

Expr1: GetBusinessDay([Enter Date], [Enter Interval])
 
Thank you so much, I have seen this before on other posts but what I was not
getting is that you need to save that code as a module. For you newbies out
there let me break it down even further so you dont have to struggle like i
did.

1. Create a table. It should only have one field called HolidayDate. Then
populate this table with whatever holidays you need to consider. I got my
holidays from:
http://www.opm.gov/Operating_Status_Schedules/fedhol/2009.asp

2. Save the table as tblHolidays

3. On your ribbon go to Database Tools and click Visual Basic

4. Go to Insert then select Module

5. Copy all text(including the disclaimer at the top)from the code here
http://www.datastrat.com/Code/GetBusinessDay.txt and paste it in your Visual
Basic window. Save the module as anything other then GetBusinessDay

6. Create a new blank query and paste into the first field box in the query
design window: Expr1: GetBusinessDay([Enter Date], [Enter Interval])

7. Save the query as whatever you want.

8. That is it! All done! Now when the query is run it will ask the user
to input a date and an interval and it will come back with a date that is
whatever interval was selected skipping weekends and whatever holidays you
put into tblHolidays.


Arvin Meyer MVP said:
The DateAdd function is quite complex, but the code is in the VBA dll. I
worked for sometime to get this function to work properly but it has served
me well for more than 10 years now:

http://www.datastrat.com/Code/GetBusinessDay.txt

Once you've saved it as a module, you can call it very simply:

Expr1: GetBusinessDay([Enter Date], [Enter Interval])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


tim.hambly said:
Hello, I have a question. I have been searching for an answer online for
the
answer with no luck. Is there a simple SQL script or query expression I
can
use to calculate business days from a date the user inputs? I have found
some
different posts on how to add business days but I beleive it is showing me
VBA scripting for a form which I am not quite understanding as I keep
getting
an error message whenever I try. Ideally what I want to achieve is there
is a
button the user clicks on and it runs this query which asks for a date.
The
user puts in the date and then the query will ask them for the interval
(just
for example the user says date of 1/1/09 and inputs 10 for the interval. I
want the query to display 1/13/09 as this is 10 business days
(Monday-Friday)
from 1/1/09. Can anyone help? I have already figured out the expression
that
will allow me to do this with just regular days and that expression is:

Expr1: DateAdd("d",[Enter Interval],[Enter Date])
Click to expand...
Click to expand...
 
And thank you Tim. This code was originally written in Access 97 where the
detailed instructions would have been quite different. Your instructions are
excellent. I might point out that the holidays table doesn't necessarily
have to agree with the "official" government calendar, nor does it require
only 1 field in tblHolidays. My table has a weekday field as well because I
use it for scheduling shipments and working days.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


tim.hambly said:
Thank you so much, I have seen this before on other posts but what I was
not
getting is that you need to save that code as a module. For you newbies
out
there let me break it down even further so you dont have to struggle like
i
did.

1. Create a table. It should only have one field called HolidayDate.
Then
populate this table with whatever holidays you need to consider. I got my
holidays from:
http://www.opm.gov/Operating_Status_Schedules/fedhol/2009.asp

2. Save the table as tblHolidays

3. On your ribbon go to Database Tools and click Visual Basic

4. Go to Insert then select Module

5. Copy all text(including the disclaimer at the top)from the code here
http://www.datastrat.com/Code/GetBusinessDay.txt and paste it in your
Visual
Basic window. Save the module as anything other then GetBusinessDay

6. Create a new blank query and paste into the first field box in the
query
design window: Expr1: GetBusinessDay([Enter Date], [Enter Interval])

7. Save the query as whatever you want.

8. That is it! All done! Now when the query is run it will ask the user
to input a date and an interval and it will come back with a date that is
whatever interval was selected skipping weekends and whatever holidays you
put into tblHolidays.


Arvin Meyer MVP said:
The DateAdd function is quite complex, but the code is in the VBA dll. I
worked for sometime to get this function to work properly but it has
served
me well for more than 10 years now:

http://www.datastrat.com/Code/GetBusinessDay.txt

Once you've saved it as a module, you can call it very simply:

Expr1: GetBusinessDay([Enter Date], [Enter Interval])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


tim.hambly said:
Hello, I have a question. I have been searching for an answer online
for
the
answer with no luck. Is there a simple SQL script or query expression I
can
use to calculate business days from a date the user inputs? I have
found
some
different posts on how to add business days but I beleive it is showing
me
VBA scripting for a form which I am not quite understanding as I keep
getting
an error message whenever I try. Ideally what I want to achieve is
there
is a
button the user clicks on and it runs this query which asks for a date.
The
user puts in the date and then the query will ask them for the interval
(just
for example the user says date of 1/1/09 and inputs 10 for the
interval. I
want the query to display 1/13/09 as this is 10 business days
(Monday-Friday)
from 1/1/09. Can anyone help? I have already figured out the expression
that
will allow me to do this with just regular days and that expression is:

Expr1: DateAdd("d",[Enter Interval],[Enter Date])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top