Hello,

Is there a way to place a text box on a form that will retrieve a Date from
the following:

OriginalDate +10 Working days (week days no weekends)
I want the +10 Working days date in the textbox. We'll call it "CutoffDate"

Thanks!!

While there are routines (try checking mvps.org/access) to "calculate"
"working days", won't that really depend on whether you have a table of
holidays. I assume you wish to eliminate not only weekends but holidays,
too.

After all, if the only thing you wanted to do was add 10 working days, you
could just add 2 weeks (since weekends add two more days each).

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:6EE58531-7018-4A5E-B67C-F4E1AD05BECB@microsoft.com...
> Hello,
>
> Is there a way to place a text box on a form that will retrieve a Date
> from
> the following:
>
> OriginalDate +10 Working days (week days no weekends)
> I want the +10 Working days date in the textbox. We'll call it
> "CutoffDate"
>
> Thanks!!

By the way, take a look at the DateAdd() function. You can use it to add
units-of-time (e.g., minutes, hours, days, weeks, etc) to a date. You can
add an event procedure to your [OriginalDate] field that calculates the new
date value and places that in your textbox.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:6EE58531-7018-4A5E-B67C-F4E1AD05BECB@microsoft.com...
> Hello,
>
> Is there a way to place a text box on a form that will retrieve a Date
> from
> the following:
>
> OriginalDate +10 Working days (week days no weekends)
> I want the +10 Working days date in the textbox. We'll call it
> "CutoffDate"
>
> Thanks!!

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

is exactly what you want.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:6EE58531-7018-4A5E-B67C-F4E1AD05BECB@microsoft.com...
> Hello,
>
> Is there a way to place a text box on a form that will retrieve a Date
> from
> the following:
>
> OriginalDate +10 Working days (week days no weekends)
> I want the +10 Working days date in the textbox. We'll call it
> "CutoffDate"
>
> Thanks!!

"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:6EE58531-7018-4A5E-B67C-F4E1AD05BECB@microsoft.com...
> Hello,
>
> Is there a way to place a text box on a form that will retrieve a Date
> from
> the following:
>
> OriginalDate +10 Working days (week days no weekends)
> I want the +10 Working days date in the textbox. We'll call it
> "CutoffDate"


(My first reply to this post doesn't seem to have made it to the newsgroup.)

See this link:

http://www.mvps.org/access/datetime/date0012.htm
Date/Time: Doing WorkDay Math in VBA

With the functions posted there saved in a standard module, you could set
the controlsource of CutoffDate to

=dhAddWorkDaysA(10, [OriginalDate])

Note that, as a calculated value, the value of CutoffDate would not normally
be saved in your form's underlying table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Guys, thank you for all your responses!!!

Jeff, you correct about the holidays. My initial thought was to use
turnaroun time in number of workdays instead of the date and place a checkbox
on the form that would subtract one from the turntime if a holiday fell
within that range. I decided to show the date instead to give the manager a
visual.

I did try the DateAdd but it only adds 10 days not 10 working days.

Dirk, I tried your recommendation and it worked fine but it was adding 10
dates to the Original date so if I have 7/15/2008 in original date,
CutoffDate displayed 7/25/2008 unless I did something wrong which I wouldn't
be surprised.

Also, you correct, it will not be placed in the table. it's more for a report.

Arvin, I tried yours and I get #Nam! in the CutoffDate box. I have this in
the control Source =GetBusinessDay(10,[OriginalDate]) Should I have it in an
event procedure instead?

Guys,

I am sure I am doing something wrong. I pasted the enitre code from each in
separate modules (Arvin Module 3, Dirk Module 5) and I can't seem to get it
right. Sorry about this. Any suggestions?

Thanks!



"Arvin Meyer [MVP]" wrote:

> http://www.datastrat.com/Code/GetBusinessDay.txt
>
> is exactly what you want.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
> news:6EE58531-7018-4A5E-B67C-F4E1AD05BECB@microsoft.com...
> > Hello,
> >
> > Is there a way to place a text box on a form that will retrieve a Date
> > from
> > the following:
> >
> > OriginalDate +10 Working days (week days no weekends)
> > I want the +10 Working days date in the textbox. We'll call it
> > "CutoffDate"
> >
> > Thanks!!
>
>
>

Hi Dirk,

I added all the code from the site and placed it in a module. I place the
line you gave me in my control source and when I open the form if it's a new
record, I get Error# in the CutoffDate field and if there is an OriginalDate
then it gives me a date in the CutoffDate field but it is 10 days from the
Original Date not 10 working days. I'm sure I must have done something wrong.

"Dirk Goldgar" wrote:

> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
> news:6EE58531-7018-4A5E-B67C-F4E1AD05BECB@microsoft.com...
> > Hello,
> >
> > Is there a way to place a text box on a form that will retrieve a Date
> > from
> > the following:
> >
> > OriginalDate +10 Working days (week days no weekends)
> > I want the +10 Working days date in the textbox. We'll call it
> > "CutoffDate"
>
>
> (My first reply to this post doesn't seem to have made it to the newsgroup.)
>
> See this link:
>
> http://www.mvps.org/access/datetime/date0012.htm
> Date/Time: Doing WorkDay Math in VBA
>
> With the functions posted there saved in a standard module, you could set
> the controlsource of CutoffDate to
>
> =dhAddWorkDaysA(10, [OriginalDate])
>
> Note that, as a calculated value, the value of CutoffDate would not normally
> be saved in your form's underlying table.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

(...)
> Is there a way to place a text box on a form that will retrieve a Date
> from
> the following:
>
> OriginalDate +10 Working days (week days no weekends)
> I want the +10 Working days date in the textbox. We'll call it
> "CutoffDate"

Write the following function:

Public Function GetD(OrigD As Date, n As Long)
Dim i As Long
Do While i < n
OrigD = OrigD + 1
If Weekday(OrigD, 2) < 6 Then i = i + 1
Loop
GetD = OrigD
End Function

How to use:

YourDate = GetD(Date, 6)

K.P.

"Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message
news:A9E247A3-C089-4305-B0EE-91605C11D4ED@microsoft.com...

> Arvin, I tried yours and I get #Nam! in the CutoffDate box. I have this in
> the control Source =GetBusinessDay(10,[OriginalDate]) Should I have it in
> an
> event procedure instead?

I usually use an event procedure, but you have it backwards anyway, and this
might fix it:


= GetBusinessDay([Forms]![Your Correct Form Name]![OriginalDate],10)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

Thank you for your reply.

I place your code in a module name Module12

The other piece under How to Use I place in the Control Source of the
textbox I want to display the CutoffDate. I changed the YourDate to Original
date as this is the date I want to be the driver. and I a get #Name? Did I do
something wrong?

"Krzysztof Pozorek [MVP]" wrote:

> (...)
> > Is there a way to place a text box on a form that will retrieve a Date
> > from
> > the following:
> >
> > OriginalDate +10 Working days (week days no weekends)
> > I want the +10 Working days date in the textbox. We'll call it
> > "CutoffDate"
>
> Write the following function:
>
> Public Function GetD(OrigD As Date, n As Long)
> Dim i As Long
> Do While i < n
> OrigD = OrigD + 1
> If Weekday(OrigD, 2) < 6 Then i = i + 1
> Loop
> GetD = OrigD
> End Function
>
> How to use:
>
> YourDate = GetD(Date, 6)
>
> K.P.
>
>
>