New to VB modules, need assistance with undefined function in expression (1 Viewer)

[Bearillastein]

I am attempting to determine workdays in a process. I can successfully do total days, but need to take out the weekends/holidays.

I've created 2 modules
The first is: Weekdays

Option Compare Database

Option Explicit

Public Function Weekdays(ByRef Process_Start_Datetime As Date, _
    ByRef Process_End_Datetime 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 Process_End_Datetime < Process_Start_Datetime Then
        dtmX = Process_Start_Datetime
        Process_Start_Datetime = Process_End_Datetime
        Process_End_Datetime = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=Process_Start_Datetime, _
        date2:=Process_End_Datetime) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=Process_Start_Datetime, _
        date2:=Process_End_Datetime) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=Process_Start_Datetime) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=Process_End_Datetime) = 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

The second module is : Workdays

[CODE]
Option Compare Database

Option Explicit

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


When entering the query, I used the following:
Workdays([Process_Start_Datetime],[Process_End_Datetime]) AS Workdays

That is when I receive the undefined function error.

--bearillastein

 

[pbaldy]

You didn't name the module the same as the function, did you? If so, can't.

 

[namliam]

I've created 2 modules
The first is: Weekdays

Option Compare Database

Option Explicit

Public Function Weekdays(ByRef Process_Start_Datetime As Date, _
    ByRef Process_End_Datetime As Date _
    ) As Integer

The second module is : Workdays

Option Compare Database

Option Explicit

Public Function Workdays(ByRef Process_Start_Datetime As Date, _
     ByRef Process_End_Datetime As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer

Which is exactly what he did, no two things can be called the same which is why we addopt a naming convention that prefixes all objects with their object type.

I.e.
tbl table
qry query
frm form
sfrm subform
rpt report
mdl module
etc.

 

[Bearillastein]

Thanks for the help. I renamed the modules and the query ran just fine.

 

[pbaldy]

Happy to help!

 

[Bearillastein]

And now for a new error...

Error 5: Invalid procedure call or argument.:banghead:

 

[Bearillastein]

I know it is from the Weekdays Error in the code. I think if I make the date/time just date and forgo the timestamp it may resolve this particular error.

 

[pr2-eugin]

Bearillastein, it would really help us, if you could provide the CODE in a proper format, with the error line highlighted !

 

[namliam]

Perhaps if one of the dates is null? (Just guessing here) this is a common problem