Calculating Working Days in Access 2003

[StryderLlama]

I found this code for Access 2007 and I cannot see any reason why it would not work in 2003, but of course, something is not right and I am sure it is my fault since I am not much of a programmer and have little knowledge about VBA.

I want to create a column in a query that takes a start date and an end date and calculates the difference but also factors out the weekends and Holidays. The code is suppossed to handle the weekends through a second function that factors out the weekends in the selected date range. It then looks at a Table I created that handles the Holidays.

When I enter my function "Working days: Workdays([Clay-Ref]![TrueSentToQADate],[Clay-Ref]![GSUQAComplDate],[Holidays])" in the Field row of the query I get a Undefined function 'Workdays' in expression error.

I have read through the code several times and cannot figure out where I am going wrong. Can anyone see what I am doing wrong. I am attaching the two functions and a picture of the Holiday table.

Workdays Function:

Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & err.Number & ": " & err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

Weekdays Function:

Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & err.Number & ": " & err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

And the table "Holidays"

 

[DJkarl]

Where is the code for the Workdays function? Is it in a public module?

 

[StryderLlama]

Yes, it is a module

Option Compare Database
Option Explicit

 

[DJkarl]

Curious.
Can you call this function from the Immediate window and see what it returns?

?Workdays(#1/1/2009#,#1/31/2009#)

 

[StryderLlama]

I get a Compile Error

Expected variable or procedure, not module

 

[DJkarl]

Sounds like you named the module and the function with the same name, if so change one to a unique name and try again.

 

[StryderLlama]

Thanks for the reply, I actually tried that yesterday morning and it fixed the problem. I was having trouble, until now, getting into the Forum. I am glad I figured it out and then come here and see that you had the same solution. I really am dense with VBA and modules. I didn't realize that modules and the function could not share the same name.

Thanks for your help.