By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,982 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

Workdate calculation Access 2000

P: n/a
I am not an experienced programmer. I do have a question regarding
workday calculations.

I have 3 fields. CURDATE, NUMDAYS, CALCDATE

After entering the first two fields, normally the current date and for
example 10 (10 day period) I need the CALCDATE to pass me the
calculated date taking into account workdays and holidays. In other
words, the 10th business day. How is this done? Thanks for any help...

PS. Reemeber, I dont have the start and end date, just the start date.
Nov 13 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
Hi Pres,
This might do it for you:
http://www.mvps.org/access/datetime/date0012.htm

HTH,
Don

"Pres" <ho***@optonline.net> wrote in message
news:57**************************@posting.google.c om...
I am not an experienced programmer. I do have a question regarding
workday calculations.

I have 3 fields. CURDATE, NUMDAYS, CALCDATE

After entering the first two fields, normally the current date and for
example 10 (10 day period) I need the CALCDATE to pass me the
calculated date taking into account workdays and holidays. In other
words, the 10th business day. How is this done? Thanks for any help...

PS. Reemeber, I dont have the start and end date, just the start date.

Nov 13 '05 #2

P: n/a
I assume I should use the first function listed on the site you offered
me. Is this typed into the code and linked to the specific fields?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
Hi,

Yes, I think the first function is what you want.
Using the instructions that are included with the code, I ran this little
test to see how this works...

From my Debug Window (Canada Day is July 1st) :
?dhAddWorkDaysA(10,Date,#07/01/2004#)
7/13/2004

Using your variables (or control names)
dhAddWorkDaysA(NumDays,CurDate,# & Holidate & #)

-- NumDays =10
-- CurDate = Date()
-- HoliDate (required by the function) = 07/01/2004

Result -- CalcDate = 07/13/2004

Don
------------------------------

"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws..

I have 3 fields. CURDATE, NUMDAYS, CALCDATE

After entering the first two fields, normally the current date and for
example 10 (10 day period) I need the CALCDATE to pass me the
calculated date taking into account workdays and holidays. In other
words, the 10th business day. How is this done? Thanks for any help...
..
I assume I should use the first function listed on the site you offered
me. Is this typed into the code and linked to the specific fields?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #4

P: n/a
Don, you have been very helpful with the time you have spent with me.
However, I am still confused (being a hands on sort of learner), I don't
know where to put this function. Does it go in the code? and if so, how
does it get called to work? I assume it can be used in a form. I do
apologize for my stupidity but everything I have learned, I have learned
on my own.

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

P: n/a
Hi Preston,

Ther are no stupid questions, only stupid answers!
(Hopefully this reply does not fall into that category.) :-)

The beauty of most functions are that you can use them almost anywhere, and
you can even "wrap" one function inside of another ... as I have here, in
the code below.

I wasn't happy with having to enter a cryptic string of dates MANUALLY as
per the Access Web example that I posted previously, so I built another
seperate function to supply the dates string for me. (Lazy or what, eh?)

Copy and Paste this code into the same module as the other "Access Web"
functions.
Examples of how it can be used are also in the coments section of the code.

My advice, (based on the advice of others that I have learned from in this
NG) is to NOT store any calculated values. Use the function throughout your
application to calculate and display the results for you.
Public Function fGetHolidates(NumDays As Long, Optional StartDate As
Variant) As String
'*******************************************
'Name: fGetHolidates (Function)
'Purpose: Intended to be used with "dhAddWorkDaysA" function from
' http://www.mvps.org/access/datetime/date0012.htm
' to supply the dates string, but it could be used stand-alone as
well
' (see example in the "output" below.)
'
'Author: Don Leverton
'Date: July 01, 2004, 10:26:32 AM
'Called by:
'Calls:
'Inputs:[NumDays],[CurrDate]
'Output:#7/1/2004#, #8/2/2004#, #9/6/2004#, #10/11/2004#

'Notes: requires the creation of a small table named
'"tblHolidates" which contains Holiday Dates for the current year.
'This example also relies on a table named "tblWorkDays", which
'contains the fields named "CurrDate", and "NumDays"

'Both of the examples shown here are showing this function wrapped
'within the "dhAddWorkDaysA()" function

'Query Usage Example:
'SELECT tblWorkDays.CurrDate, tblWorkDays.NumDays,
'dhAddWorkDaysA([NumDays],[CurrDate],fGetHolidates([NumDays],[CurrDate])) AS
ResultDate
'FROM tblWorkDays;

'Form/Report Usage Example:
'Use
"=dhAddWorkDaysA([NumDays],[CurrDate],fGetHolidates([NumDays],[CurrDate]))"
' as the Control Source for an Unbound Text-box on a form which is bound to
tblWorkDays
'*******************************************
On Error GoTo ErrHandler

Dim MyDB As DAO.Database
Set MyDB = CurrentDb

Dim rst As DAO.Recordset

Dim EndDate As Date
Dim MySQL As String
Dim strHoliDates As String

'Get StartDate and EndDate
If Not IsDate(StartDate) Then
StartDate = Date
End If
EndDate = DateAdd("d", NumDays, StartDate)
MySQL = ""
MySQL = MySQL & "SELECT tblHolidates.Holidate "
MySQL = MySQL & "FROM tblHolidates "
MySQL = MySQL & "WHERE (((tblHolidates.Holidate) Between #"
MySQL = MySQL & StartDate
MySQL = MySQL & "# And #"
MySQL = MySQL & EndDate
MySQL = MySQL & "#));"

Set rst = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
With rst
If Not .BOF Then
.MoveLast
.MoveFirst
Do Until .EOF
If Len(strHoliDates) > 0 Then
strHoliDates = strHoliDates & ", #" & !Holidate & "#"
Else
strHoliDates = "#" & !Holidate & "#"
End If

If Not .EOF Then
.MoveNext
End If
Loop

Else
Exit Function
End If
..Close
End With

Set rst = Nothing
Set MyDB = Nothing

fGetHolidates = strHoliDates

ExitHere:
Exit Function
ErrHandler:
Dim strErrString As String
strErrString = "Error Information..." & vbCrLf
strErrString = strErrString & "Error#: " & Err.Number
strErrString = strErrString & "Description: " & Err.Description
MsgBox strErrString, vbCritical + vbOKOnly, "Function: fGetHolidates"
Resume ExitHere

End Function
*******************************************


"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .
Don, you have been very helpful with the time you have spent with me.
However, I am still confused (being a hands on sort of learner), I don't
know where to put this function. Does it go in the code? and if so, how
does it get called to work? I assume it can be used in a form. I do
apologize for my stupidity but everything I have learned, I have learned
on my own.

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #6

P: n/a
Don,
in case you want to test your work, Arvin Meyer has a function that
does this on his website. www.datastrat.com Uses a table instead of
an array.
Nov 13 '05 #7

P: n/a
Don,
in case you want to test your work, Arvin Meyer has a function that
does this on his website. www.datastrat.com Uses a table instead of
an array.
Nov 13 '05 #8

P: n/a
Hi Pieter,

I assume that you meant http://www.datastrat.com/Code/GetBusinessDay.txt ?

Yeah, I see Arvin's method is a lot more direct than mine. :-)
For the record though, I was also using a table ... just building an "array"
from the records therein.
(re-inventing the wheel or what!)

Preston, if you're still following this thread ... use Arvin's code instead
of mine.
I wish I had seen this first ...

Implementing it is quite a bit easier:
Result: GetBusinessDay([CurrDate],[NumDays])

Thx,
Don

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
Don,
in case you want to test your work, Arvin Meyer has a function that
does this on his website. www.datastrat.com Uses a table instead of
an array.

Nov 13 '05 #9

P: n/a
Don and Pietr

I am totally confused.

Can we rehash just which functions I need, the old one, the newone,
yours, a combination of both?

Do the field names have to be changed in the function?

Just where do I place the code?

How do I use the function to make the calculation appear?

I am sure you guys are geniuses at what you do and I appreciate all the
help, but I must be anal retentive. Thanks loads and have a great July
4th.

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #10

P: n/a
Preston

See comments inserted in your post.
"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .
Don and Pietr

I am totally confused.
I can see that. <grin>

Can we rehash just which functions I need, the old one, the newone,
yours, a combination of both?
Use Arvin Meyer's function from
http://www.datastrat.com/Code/GetBusinessDay.txt


Do the field names have to be changed in the function?
Yes, that would be the logical thing to do. Base your Form, Report, or Query
on "tblWorkdays" (if that is what you're calling it) so that the function
has access to the data from the fields.


Just where do I place the code?
1.) Copy the text from http://www.datastrat.com/Code/GetBusinessDay.txt
2.) Switch to Access, and open the Database Window
3.) Select the "Modules" tab
4.) Click [New]
5.) Paste the code into the new code window
6.) Click "Debug" on the menu bar, then select "Compile Loaded Modules"
7.) Click "File", then "Save"
8.) Type in a name for this newly-created module ... something like
"modWorkDays"
9.) Close the Module code window.

How do I use the function to make the calculation appear?
Because we have that function in a main module (rather than a Class Module
that is specific to just one form or report) can use that fuction (I repeat)
almost anywhere ...

In a form/report that is bound to "tblWorkdays", set the UNBOUND textbox
named "txtCalcDate" Control Source to:
= GetBusinessDay([CurrDate],[NumDays])

In a query:
1.) Create a new query based on "tblWorkdays"
2.) Switch to SQL view
3.) Provided that your table really is named "tblWorkdays", you could copy
this SQL string and paste it in to replace what is there now.

SELECT tblWorkDays.CurrDate, tblWorkDays.NumDays,
GetBusinessDay([CurrDate],[NumDays]) AS ResultDay
FROM tblWorkDays;

4.) If the table isn't named tblWorkDays, then I'd suggest switching to
Design view and just typing "GetBusinessDay([CurrDate],[NumDays])" into the
Field row of a new column in the query grid.
Access will name this field "Expr1" and you'll now see:
"Expr1:GetBusinessDay([CurrDate],[NumDays])" in the Field row.
Rename "Expr1" to something more meaninful like "ResultDay".

5.) Switch to Datasheet view, and you'll see the calculated date (which is
CurrDate + NumDays - Holidays) in the "ResultDay" column.

You can use the Expression Builder to help you build the correct syntax if
you want to create something new as well.
Look for "Functions", and click on the "+". You want to look at the Other
functions (Not "Built-In Functions"), where you should see your
"GetBusinessDay" function. The builder will then display prompts for the
requirements of the function "GetBusinessDay («datStart», «intDayAdd») "
I am sure you guys are geniuses at what you do and I appreciate all the
help, but I must be anal retentive. Thanks loads and have a great July
4th.


I'm Canadian, and I already had a great July 1st ... Canada Day, thanks.
Calgary, Alberta Canada. Home of the greatest hockey team in the world ...
even if they didn't QUITE win the Stanley Cup.
"Just wait 'til next year" <grin>

--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
Nov 13 '05 #11

P: n/a
Don,

I've been trying to post all weekend and these replies keep going into
the deep abyss, never to be read again. Here goes for the 4th time:

I get a compile error - User Defined type not defined on line: DIM rst
AS DAO Recordset.

What does this mean?

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #12

P: n/a
Pres
You're missing the "."
Dim rst As DAO.Recordset (that's DAO dot Recordset)

Don

"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .
Don,

I've been trying to post all weekend and these replies keep going into
the deep abyss, never to be read again. Here goes for the 4th time:

I get a compile error - User Defined type not defined on line: DIM rst
AS DAO Recordset.

What does this mean?

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #13

P: n/a
Don, Don, Don

Thi site is strange. None of my mesaages have posted for 5 days. So I
try again.

The "dot" was properly placed. I copied the code directly into the
module but alas I get that error. What is the trouble, if you ever get
this message.

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #14

P: n/a
Hi Preston,

Your message:
I get a compile error - User Defined type not defined on line: DIM rst
AS DAO Recordset. WAS missing the dot, and I assumed that you had copied and pasted it from
your code window.

Now I believe that perhaps you don't have the DAO component installed ...
this is because Access 2000 uses ADO by default.

This is explained and instruction on how to install DAO can be found in this
KB article:
http://support.microsoft.com/default...roduct=acc2000

Douglas Steele also has some good information related to this topic:
http://members.rogers.com/douglas.j....nceErrors.html

Give that a try.

Don

PS
If devdex.com is not avaialable or reliable, you could use Google, or even
Outlook Express as a newreader program.
("Choice of newsreader" opinions that will likely follow may carry this
threads on forever.) <grin>

================

"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. . Don, Don, Don

Thi site is strange. None of my mesaages have posted for 5 days. So I
try again.

The "dot" was properly placed. I copied the code directly into the
module but alas I get that error. What is the trouble, if you ever get
this message.

Preston Holod

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #15

P: n/a
Don

You are a genius. Thanks for your help. NOw if you can only help with
my other questions, I would begin celebrating Canada Day immediately.
Thanks again

Preston Holod

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.