473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Workdate calculation Access 2000

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
15 3571
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Terry | last post by:
I have used the code provided in the PreciseDateDiff function at the following Access Web link to calculate the time change date (from Daylight to Standard time and vice versa): ...
2
by: Del | last post by:
Thanks in advance for any help. I have a database that was created in Access 2000. Several users have been upgraded to Access 2003. Since upgrading to 2003 we have noticed that some of the...
1
by: cdelaney | last post by:
I have a form that I created a calculation on using 2003. The calculation works exactly like I want it to but ONLY on the first and last record. The calculation does not work/exist on records in...
2
by: DebbieG | last post by:
I have no idea how to start with this one. I have a subform where records could look similar to: Infraction Date Points 01/01/2000 3 06/01/2002 1 Somehow, I've got to...
6
by: gsb58 | last post by:
Hi! Recently we, in Norway changed to three different VAT levels. All three needs to be on the invoice program. This is easy obtained via a new field and set the rowsource to valuelist and make...
1
by: gjoneshtfc | last post by:
Hello, I have an asp page that is a table. In the table the end column is a calculation of other values, for example: ...
1
by: Colin Clark | last post by:
Hello, I have a database of scores for a sports club. I want to display a continuous subform showing a person's scores for the season along with a running calculation based on those scores (the...
6
by: andrewdb | last post by:
Hello, I needed help in finding how can I get access to to automatically populate a column where the value is just simple arithmetics between two other columns. For example: DIFF = Col4 - Col5...
1
by: =?Utf-8?B?a2FyaW0=?= | last post by:
Hello All, my question is how to get a text box or a label to display decimal #, like 0.9 or 1.4. what i'm trying to do is to convert from, for example, steps to miles. if 1 mile = 2000 steps,...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.