RE: Calculating Holidays

Klatuu

My form has the following fields:

Number (Autonumber)
FirstName (Text)
dtmStart (Date)
DtmEnd (Date)
WorkDays (Number)

I want to determine the number of work days inbetween and including dtmStart
and DtmEnd—excluding Saturday and Sunday. At the moment I am not interested
in removing statutory holidays.

This is my standard module:

Public Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function


This is my module attached toAfterUpDate in the WorkDays field in my form:

Private Sub DtmEnd_AfterUpdate()
CalcWorkDays
End Sub

When I set a breakpoint at Private Sub DtmEnd_AfterUpdate(), it moves to
CalcWorkDays and then I get the following error:

Compile Error
Argument not optional

What am I doing incorrectly?
Fay

"Klatuu" wrote:

Fay,

Here is a version of what you are trying to do. Rather than have to user
have to determine the number of holidays and enter them, create a holiday
table. All you need is the date of the holiday, but for convenience, mine
has a description of the holiday. Now all you need to enter is the start
date and end date, and this function will return the number of "working days"
between the two dates.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, DtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, DtmEnd) - _
(DateDiff("ww", dtmStart, DtmEnd, vbSaturday) + _
DateDiff("ww", dtmStart, DtmEnd, vbSunday)) + 1

'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "tblHolidays", _
"[HOLI_DATE] between #" & dtmStart & "# And #" & DtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


"Fay" wrote:

My form contains these fields:

Number (Autonumber)
FirstName (Text)
StartDate (Date)
EndDate (Date)
Holidays (Number)

I want to calculate the number of days inbetween and including StartDate and
EndDate—excluding any Saturdays and Sundays.

This is my standard module.

Public Sub CalculateNoHolidays()

On Error GoTo Err_Holidays

Dim intCount As Integer
Dim Holidays As Integer

'StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
Holidays = intCount

Exit_Holidays:
Exit Sub

Err_Holidays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_Holidays
End Select

This is the code I created for the EndDate field in AfterUpDate. It is
supposed to call up the standard procedure.

Private Sub EndDate_AfterUpdate()
CalculateNoHolidays
End Sub

I set a breakpoint at Private Sub EndDate_AfterUpdate() to test the code.

What happens is that when it switches over to the standard module, it looks
at the first date—goes back to the Do While StartDate <= EndDate and then
goes out of the loop to Holidays = intCount Holidays show as a zero.

What is wrong?

Please be patient—I am just starting to learn VBA—I am a senior—don’t use
big technical words. Thank you.

.

Relevant Pages

  • Re: Workdays function with Australian Dates
    ... Web, but as I am in Australia and my date format is dd/mm/yyyy, I have ... found that the dates I put in my holidays table are reversed into American ... ' Inputs: StartDate As Date ... Dim intCount As Integer ...
    (comp.databases.ms-access)
  • Re: Working Day calculations
    ... ' Inputs: StartDate As Date ... ' Note that this function has been modified to account for holidays. ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Workdays Function : Repost
    ... which includes provision for holidays (I also had to select Microsoft DAO 3.6 ... EndDate As Date) As Integer ... ' Inputs: StartDate As Date ... Dim intCount As Integer ...
    (microsoft.public.access.formscoding)
  • Working Day calculations
    ... ' Inputs: StartDate As Date ... ' Note that this function has been modified to account for holidays. ... Dim intCount As Integer ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • RE: Calculate working days between dates?
    ... dtmEnd As Date) As Integer ... Dim intTotalDays As Integer ' Counter for number of days ... > ' Note that this function has been modified to account for holidays. ... > 'To count StartDate as the 1st day comment out the line above ...
    (microsoft.public.access.formscoding)
Loading