G
Guest
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?
2 dates and use this for present dates and future dates?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dirk Goldgar
"Week Day Calculation" <Week Day (e-mail address removed)>
wrote in message
There are routines to do this posted here:
http://www.mvps.org/access/datetime/date0006.htm
and here:
http://www.mvps.org/access/datetime/date0012.htm
wrote in message
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?
There are routines to do this posted here:
http://www.mvps.org/access/datetime/date0006.htm
and here:
http://www.mvps.org/access/datetime/date0012.htm
G
Guest
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?
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
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.
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?
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.
G
Guest
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
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
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
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
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
G
Guest
The error I received is Undefined Funtion 'WorkDays' in expression.
D
Dirk Goldgar
"Week Day Calculation" <[email protected]>
wrote in message
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.
wrote in message
The error I received is Undefined Funtion 'WorkDays' in expression.
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.
G
Guest
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.
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
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".
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.
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".
G
Guest
I have made this change but still get no Debug / Compile, nor does the "Step
Into" work.
Into" work.
D
Dirk Goldgar
"Week Day Calculation" <[email protected]>
wrote in message
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>?
wrote in message
I have made this change but still get no Debug / Compile, nor does
the "Step Into" work.
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>?
G
Guest
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.
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
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.
Ah, very good.
You're welcome.
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.
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.
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.
You're welcome.
G
Guest
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
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.
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.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.