Results 1 to 9 of 9
  1. #1
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228

    undefined function in query

    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:

    Code:
    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

  2. #2
    randman1 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    25
    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

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    No, don't remove the 's'. Weekdays is another custom function from that same article that needs to be installed in the module.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    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

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    The Workdays function is calling Weekdays function. Both are custom functions from that article.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    randman1 is offline Novice
    Windows XP Access 2003
    Join Date
    Dec 2009
    Posts
    25
    -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?

    Code:
    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

  7. #7
    mejia.j88 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    california
    Posts
    228
    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

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,422
    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).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    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()

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Undefined Function "Left" in Expression
    By krueck in forum Access
    Replies: 4
    Last Post: 09-30-2011, 10:50 AM
  2. Date() function undefined
    By Bruce in forum Queries
    Replies: 4
    Last Post: 07-28-2011, 04:53 PM
  3. Replies: 4
    Last Post: 11-19-2010, 07:21 PM
  4. Undefined error importing spreadsheet
    By kbremner in forum Import/Export Data
    Replies: 1
    Last Post: 10-23-2010, 05:57 PM
  5. Count function on query
    By yousillygoose in forum Queries
    Replies: 1
    Last Post: 02-15-2010, 09:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums