Calculate work days between 2 dates using Access 2002

  • Thread starter Week Day Calculation
  • Start date
W

Week Day Calculation

How can I calculate the number of work days (no Sat, Sun or Holidays) between
2 dates and use this for present dates and future dates?
 
W

Week Day Calculation

Thank you, Dirk,
I have added a public function using the Holiday Table, however the DAO.
stmts are stopping this function from working. Do you have any suggestions?
 
D

Dirk Goldgar

"Week Day Calculation" <[email protected]>
wrote in message
Thank you, Dirk,
I have added a public function using the Holiday Table, however the
DAO. stmts are stopping this function from working. Do you have any
suggestions?
Click to expand...

While editing the code, click menu items Tools -> References..., find
"Microsoft DAO 3.6 Object Library" in the list, and put a check mark in
the box next to it, then click the OK button to close the dialog. If
this reference already has a check mark next to it, something else is
wrong and you'll have to tell me exactly what error message you get.
 
W

Week Day Calculation

I apologize in advance because I haven't used functions in much capacity. I
am pasting the code and also the error I receive after applying this function
to my query. I appreciate all your help. Ginger


Test Query:
NumDays: WorkDays([Est Close],[Approval Date])




Public Function WorkDays(StartDate As Date, EndDate As Date) As Integer


'Name: WorkDays
'Inputs: StartDate As Date
' EndDate As Date
'Returns: Integer
'Author: Ginger Frye
'Date: August 16, 2005
'Comment: Accepts two dates and returns the number of weekdays between them
'Note that this function will account for holidays. It requires a table
'named Holidays tbl with a field named HolidayDate.
'---------------------------------------------------------------------------

On Error GoTo Err_WorkDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate]FROM Holidays tbl",
dbOpenSnapshot)

'StartDate = StartDate+1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate]= #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkDays = intCount

Exit_WorkDays:
Exit Function

Err_WorkDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDays
End Select

End Function
 
D

Dirk Goldgar

"Week Day Calculation" <[email protected]>
wrote in message
I apologize in advance because I haven't used functions in much
capacity. I am pasting the code and also the error I receive after
applying this function to my query. I appreciate all your help.
Ginger


Test Query:
NumDays: WorkDays([Est Close],[Approval Date])




Public Function WorkDays(StartDate As Date, EndDate As Date) As
Integer


'Name: WorkDays
'Inputs: StartDate As Date
' EndDate As Date
'Returns: Integer
'Author: Ginger Frye
'Date: August 16, 2005
'Comment: Accepts two dates and returns the number of weekdays
between them 'Note that this function will account for holidays. It
requires a table 'named Holidays tbl with a field named HolidayDate.
'-----------------------------------------------------------------------
----

On Error GoTo Err_WorkDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate]FROM Holidays tbl",
dbOpenSnapshot)

'StartDate = StartDate+1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate]= #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <>
vbSaturday Then If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkDays = intCount

Exit_WorkDays:
Exit Function

Err_WorkDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkDays
End Select

End Function
Click to expand...

Ginger -

You said you were posting the error your were getting, but I don't see
it in your post. What is it?

Seeing as how the function you posted is only slightly modified from the
version I pointed you to, written by Arvin Meyer, I don't think you
should claim complete authorship in your comments. In cases like this,
I usually comment it along the lines of

'Original code written by Arvin Meyer on February 19, 1997
'Adapted and modified by Ginger Frye on August 16, 2005
 
D

Dirk Goldgar

"Week Day Calculation" <[email protected]>
wrote in message
The error I received is Undefined Funtion 'WorkDays' in expression.
Click to expand...

What happens when you compile the database, using the Debug -> Compile
menu items in the VB Editor environment? Are any compile errors found?

What is the name of the module in which you defined the function? It
has to be a standard module, not a form or report module, and it must
not have the same name as the function itself.
 
W

Week Day Calculation

It does nothing when I try to Debug / Compile. The module name is WorkDays
and the box at the left (when in design view) shows (general). This is a
drop down box with no dropdown items available.
 
D

Dirk Goldgar

"Week Day Calculation" <[email protected]>
wrote in message
It does nothing when I try to Debug / Compile. The module name is
WorkDays and the box at the left (when in design view) shows
(general). This is a drop down box with no dropdown items available.
Click to expand...

As I said, the module name *must not* be the same as the name of the
function. Let's be clear: the module is the "container" for the
function. The function is named "WorkDays". Give the module a
different name. I suggest you name the module "modWorkDays".
 
W

Week Day Calculation

I have made this change but still get no Debug / Compile, nor does the "Step
Into" work.
 
D

Dirk Goldgar

"Week Day Calculation" <[email protected]>
wrote in message
I have made this change but still get no Debug / Compile, nor does
the "Step Into" work.
Click to expand...

You can't Step Into a function that has arguments, so that's no
surprise. What do you mean when you say you "get no Debug / Compile"?
Are you sayting that you are looking at the code window in the VB Editor
environment, and you don't get a menu item for Debug -> Compile <your
project name>?
 
W

Week Day Calculation

I thought this function would do a "compile" and possibly note anything
missing, but like I said, I am not an advanced user of Modules and using
Functions. I did go back to the query and ran it. It will now give me
information and I have verified the data it returns. It looks like I have a
working function now. Thank you so much for your help. I will not claim
myself as the author....that was not my intention...I labeled it that way for
internal company purposes. Again thank you for your help.
 
D

Dirk Goldgar

"Week Day Calculation" <[email protected]>
wrote in message
I thought this function would do a "compile" and possibly note
anything missing, but like I said, I am not an advanced user of
Modules and using Functions.
Click to expand...

You'd only get a message if there was a compile error. And if the
project was already fully compiled, the Compile option would be grayed
out. I expect that's what you were seeing.
I did go back to the query and ran it.
It will now give me information and I have verified the data it
returns. It looks like I have a working function now.
Click to expand...

Ah, very good.
Thank you so
much for your help. I will not claim myself as the author....that
was not my intention...I labeled it that way for internal company
purposes. Again thank you for your help.
Click to expand...

You're welcome.
 
S

Sam

Dirk

I have read this thread and found it to be most useful! I too had a problem
with eliminating weekends and after reading this thread have worked out what
I was going wrong (Thanks Heaps)

I do however have a problem with this code calculating the number of days
when a date field is left blank. I will expand on this:-

I have seven date fields in my data base. When a user places the file into a
differnet status, it auto records a date in one of these fields based on a
calculation between the data data entered and the date the file was placed in
the status. I have created a query based on these tables.

There are occasions where some or all of these fields will not need to have
a date inserted (actually these would be at least one that would be
populated) Where there is no date populated I get "#Error" where the date
field is blank.

How can I overcome this problem?? Should I set up the table to default a
zero (0) in the field so that in the event that there is no Data a 0 is
listed or is there a way to adapt the code below to compensate for this?

I hope my explanantion has not confused you, however if I have not explained
myself correctly, I will gladly expand on this if need be.

Thnaking you in advance

Sam
 
D

Dirk Goldgar

Sam said:
Dirk

I have read this thread and found it to be most useful! I too had a
problem with eliminating weekends and after reading this thread have
worked out what I was going wrong (Thanks Heaps)

I do however have a problem with this code calculating the number of
days when a date field is left blank. I will expand on this:-

I have seven date fields in my data base. When a user places the file
into a differnet status, it auto records a date in one of these
fields based on a calculation between the data data entered and the
date the file was placed in the status. I have created a query based
on these tables.

There are occasions where some or all of these fields will not need
to have a date inserted (actually these would be at least one that
would be populated) Where there is no date populated I get "#Error"
where the date field is blank.

How can I overcome this problem?? Should I set up the table to
default a zero (0) in the field so that in the event that there is no
Data a 0 is listed or is there a way to adapt the code below to
compensate for this?

I hope my explanantion has not confused you, however if I have not
explained myself correctly, I will gladly expand on this if need be.
Click to expand...

Hi, Sam. Sorry I didn't see this message right away; it was hiding at
the bottom of my list because you tacked it onto an old thread.

I think your problem will probably be solved fairly easily, but
unfortunately, I don't think I have quite enough information to do it
yet. Where do you get the #Error value? I assume this is happening in
one or more calculated controls. What are the controlsource expressions
in those controls? For that matter, why don't you list all the controls
involved, give the controlsource of each (whether bound or calculated),
and note which ones give you #Error under which circumstances?

Please also post the code of the exact version of the function that
you're using. That'll help if it comes down to modifying the function.

Given that the #Error occurs because one of the required dates is
missing, what would you want to have displayed in the calculated field?
It would make sense for it to be blank (Null), but maybe you want to
assume some value for the missing date field(s), such that you can still
calculate a value for the work days.
 
Top