Date plus 10 Working days

  • Thread starter Thread starter Stockwell43
  • Start date Start date
Full disclosure: The last time I worked on Access was in 1997 :)

Guys, I need a little help. I'm trying to accomplish something similiar to what was posted earlier: I'd like to have 3 fields: "Original Date" (standard date... 00/00/00), Add Days (user would enter a number... e.g., "2", and "Cut off Date" (calculates, but doesn't store, Original Date plus Add Days' number, in working days) and displays the value into "Cut off Date."

My problems are many. To start, I am not sure how to call up the module that Dirk references (http://www.mvps.org/access/datetime/date0012.htm) into my form. I copy/pasted it using VB, and named it something, but can't figure out how to associate it from the form I created. Once I've save it, how do I find it and link it to my form?

Second, I suspect the above code doesn't quite address how I would use a second user input field for how many days to add.

Also, I can't figure out how to implement Dirk's suggestion to: "With the functions posted there saved in a standard module, you could set
the controlsource of CutoffDate to
=dhAddWorkDaysA(10, [OriginalDate])".

I went to the field in my form (called Cut off date), clicked on its properties, clicked in control source for that field, and pasted in Dirk's language: =dhAddWorkDaysA(10, [OriginalDate])

It wouldn't accept it. I tried using expression builder but to no avail.

I feel as if I'm missing many, many basic things here. If my question is too involved, please feel free to ignore.
 
Quick question-- how do I adjust this line, if I want to include Saturdays
(Just not include Sundays and Holidays). I can't seem to grasp the meaning
of "<>".

"If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1"

Thank you for your time and any info. -Diane
--------------------------------------------------------
Out of clutter, find Simplicity.
From discord, find Harmony.
In the middle of difficulty lies opportunity.
Albert Einstein
----------------------------------------------------------


Dirk Goldgar said:
(re-posting, as my original message hasn't appeared)

Full disclosure: The last time I worked on Access was in 1997 :)

Guys, I need a little help. I'm trying to accomplish something similiar to
what was posted earlier: I'd like to have 3 fields: "Original Date"
(standard date... 00/00/00), Add Days (user would enter a number... e.g.,
"2", and "Cut off Date" (calculates, but doesn't store, Original Date plus
Add Days' number, in working days) and displays the value into "Cut off
Date."

My problems are many. To start, I am not sure how to call up the module
that Dirk references (http://www.mvps.org/access/datetime/date0012.htm)
into my form. I copy/pasted it using VB, and named it something, but can't
figure out how to associate it from the form I created. Once I've save it,
how do I find it and link it to my form?
Click to expand...

Did you paste the code, everything between "**** Code Start ****" and "****
Code End ****", into a standard module, one that appears on the Modules tab
of the database window? Did you save that module. When you save it, make
sure you don't give the module the same name as any of the functions it
contains.

You don't have to do anything to link the module to your form.
Second, I suspect the above code doesn't quite address how I would use a
second user input field for how many days to add.
Click to expand...

Not directly, but you could easily pass the second field in the expression
that calls the function. See below.
Also, I can't figure out how to implement Dirk's suggestion to: "With the
functions posted there saved in a standard module, you could set
the controlsource of CutoffDate to
=dhAddWorkDaysA(10, [OriginalDate])".

I went to the field in my form (called Cut off date), clicked on its
properties, clicked in control source for that field, and pasted in Dirk's
language: =dhAddWorkDaysA(10, [OriginalDate])
Click to expand...

What is tha actual name of the control that contains the base date? Is it
"OriginalDate", as in the example I gave, or is it "Original Date" as you
said above, or is it something else again?

If the text box displaying the base date is named "Original Date", and the
text box containing the number of days to add is called "Add Days" as you
said above, then your controlsource expression might be:

=dhAddWorkDaysA([Add Days], [Original Date])

If Access won't accept that, what error message does it give you?

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

(please reply to the newsgroup)
Click to expand...
 
Hi,

Before I start please excuse me but my hash key is not working on the
keyboard I am using to type this message so I'll replace it with a pound sign
for now.

I have included the functions posted on the link
http://www.mvps.org/access/datetime/date0012.htm and it works fine
calculating a date a number of working days in the future when calling the
function dhAddWorkDaysA(10, £2/9/2000£), but as soon as I try to take account
of holidays as in the example in the code - dhAddWorkDaysA(10, £2/9/2000£,
Array(£2/16/2000£, £2/17/2000£)) - I get an error "Data Mismatch in Criteria
Expression".

I am in the UK and so use dmyy rather than mdyy but have tried all
variations to no avail. Could anyone please advise as I am getting quite
frustrated with it.

Cheers,

Andy
:
 
That code does work fine, but try this one:

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

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


AndyC said:
Hi,

Before I start please excuse me but my hash key is not working on the
keyboard I am using to type this message so I'll replace it with a pound
sign
for now.

I have included the functions posted on the link
http://www.mvps.org/access/datetime/date0012.htm and it works fine
calculating a date a number of working days in the future when calling the
function dhAddWorkDaysA(10, £2/9/2000£), but as soon as I try to take
account
of holidays as in the example in the code - dhAddWorkDaysA(10, £2/9/2000£,
Array(£2/16/2000£, £2/17/2000£)) - I get an error "Data Mismatch in
Criteria
Expression".

I am in the UK and so use dmyy rather than mdyy but have tried all
variations to no avail. Could anyone please advise as I am getting quite
frustrated with it.

Cheers,

Andy
:

(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)
Click to expand...
Click to expand...
 
Back
Top