undefined function in query

[mejia.j88]

hello all,



i have used the following ( http://msdn.microsoft.com/en-us/libr...ingWorkingDays ) function to calculate workdays and exclude holidays in a query.

i have saved the function under the folder Modules in VB,

here is the following code used:

Option Explicit

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

every time i try to run the query, it gives me the Undefined function "workdays" in expression.
my question is if i have saved the function correctly in VB?

many thanks,
j

[randman1]

I couldn't get a return cause I don't have a table named [Holidays] but did get a value for nWeekdays after making the change below:

Line 18: nWeekdays = Weekdays(startDate, endDate)

Change to: nWeekdays = Weekday(startDate, endDate) '<--- Remove the 's' in Weekdays

[June7]

No, don't remove the 's'. Weekdays is another custom function from that same article that needs to be installed in the module.

[mejia.j88]

Hi Randman,
Thank you for your reply. I'm not sure if that is causing the problem I have.
When I try to run the query, it simply gives me the "Undefined function "workdays" in expression."

I do not think that my query is calling the function?
It will not even open VB to show errors in the code. It is just simply not calling it.

-J

[June7]

The Workdays function is calling Weekdays function. Both are custom functions from that article.

[randman1]

-J

June is right, there is another function on that same page that I missed. Did you insert the Weekdays function in the same module?

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

[mejia.j88]

hi guys,

thank you so much for your help.

i did have the weekdays function in a different module.
i removed it and added it to the same module that has workdays and now it is working.
does it matter which modules the functions are saved to?

-j

[June7]

It should not matter if the procedures are in different general (standard) modules, as long as the procedure is Public (Public is default if not explicitely declared). Procedures in general modules can be called from any other procedure and functions can also be called by Access (queries, textboxes).

[boblarson]

They have to be in STANDARD modules, not form, report, or class modules if you want to use them from other places than the module they are in and not use qualifying references (i.e. FormNameHere.ProdecureNameHere). And you can either user

Public Function xxxx()


or

you can just use

Function xxxx()

If you don't use anything in front of it, it is Public by default. It only becomes Private if you use

Private Function xxxx()