Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calcualting Number of Weekdays exckuding holidays

    I have an expression in a query that worked using DateDiffW. It did not exclude holidays. I found the function "WorkingDays2" on the Access Web that takes into account holidays. Whenever I attempt to run it I get an undefined function error message. Below is my query and the code in the function from the Access Web. Anyone know what I am doing wrong? Thanks for the help.

    Gary O

    Tickets/Day: [Total Tickets]/(WorkingDays([Forms].[frmCalendar].[txtStartDate],[Forms].[frmCalendar].[txtEndDate])+1)


    Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
    '................................................. ...................
    ' Name: WorkingDays2
    ' Inputs: StartDate As Date
    ' EndDate As Date
    ' Returns: Integer
    ' Author: Arvin Meyer
    ' Date: May 5,2002
    ' Comment: Accepts two dates and returns the number of weekdays between them
    ' Note that this function has been modified to account for holidays. It requires a table
    ' named tblHolidays with a field named HolidayDate.
    '................................................. ...................
    On Error GoTo Err_WorkingDays2

    Dim intCount As Integer
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database

    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

    'StartDate = StartDate + 1
    'To count StartDate as the 1st day comment out the line above

    intCount = 0

    Do While StartDate <= EndDate

    rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
    If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
    If rst.NoMatch Then intCount = intCount + 1
    End If

    StartDate = StartDate + 1

    Loop

    WorkingDays2 = intCount

    Exit_WorkingDays2:
    Exit Function

    Err_WorkingDays2:
    Select Case Err

    Case Else
    MsgBox Err.Description
    Resume Exit_WorkingDays2
    End Select

    End Function

    '*********** Code End **************

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calcualting Number of Weekdays exckuding holidays

    The function is named WorkingDays2, but your expression uses WorkingDays.

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calcualting Number of Weekdays excluding holid

    That was just a typo. I am using WorkingDays2 in the query.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calcualting Number of Weekdays excluding holid

    The function uses DAO. Select Tools | References... in the Visual Basic Editor and make sure that Microsoft DAO 3.6 Object Library is ticked (version 3.6 is for Access 2000 and later, for Access 97 it is version 3.51). Does that solve the problem?

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Location
    Greenville, North Carolina, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calcualting Number of Weekdays excluding holid

    No, I already have that checked.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calcualting Number of Weekdays excluding holid

    Do you happen to have a module named WorkingDays2? That would confuse Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •