You sure you've got tblHolidays populated properly?
While I know you've got time invested in this already, you could also
check
out my September, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column (and sample database) for
free
at
http://www.accessmvp.com/DJSteele/SmartAccess.html
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Doug,
Your question made me take a second look at my Days ControlSource and
yep
that was the problem. This is what it is now
=workingdays2([startdate],[enddate])
New Problem: The working days are being calculated correctly, but the
public holdidays are not being taken into consideration.
example: 26/01/2005 is Australia Day and i have this in the HolidayDate
field of the table tblHolidays. When i type in the dates 17/01/2005 to
28/01/2005 i get 10 working days, therefore not taking the 26/01/2005
as a
holiday.
Any thoughts?
:
And how are you using WorkingDays2? How are you calling it in code (or
in
a
query)?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Doug,
Below is the following code pasted into a module called
mdlWorkingDays2.
I have a form called Leave with Fields
LeaveID (primarykey)
StaffID (combobox)
StartDate
EndDate
Days
I have a table called tblHolidays with fields
HolidayDate (primarykey)
Holiday
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between
them
' Note that this function has been modified to account for holidays.
It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
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
WorkingDays2 = intCount
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function
:
What exactly did you paste as your code?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi Doug,
Ok the #Name? has now disappeared, but code doesn't give any
answer
in
the
Days field.
Further to my last reply, shold the me a formula in the control
source
of
the days field?
:
Rename your module, say to mdlWorkingDays2. You can't name a
module
the
same
as procedures that exist within it.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Lynn,
Thanks for that, i've created everthing which i believe is
correct.
I'm coming up with #Name? in the the field Days on my form.
I have a form with StartDate, EndDate and Days.
I have a table tblHolidays with HolidayDate.
I have created a module called WorkingDays2 and pasted the
code
there.
Is there anything i'm missing?
:
http://www.mvps.org/access/datetime/date0006.htm
This site has 2 functions. One that accounts for holidays
and
one
that
does
not.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security:
www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
I am relatively a novice when it comes to coding.
I'm looking for some code that allows me to calculate the
business
days
between two dates, excluding public holidays.
Regards,
Greg
