Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    WorkDays (A2000 SR1)

    The following function calculates Work days, If their is 1 date in the range ie.
    BookInDate: 29/08/02
    BookOutDate: 29/08/02
    The function returns 0 where as it should return 1.
    Can the code be modified to remedy this.
    Dave


    Public Function WeekdaysMinusHolidays(BookInDate, BookOutDate) As Long

    ' From Kristi at the ZDJournals Forum
    ' http://bbs.zdjournals.com/
    ' Returns the number of workdays
    ' between BegDate and EndDate. Returns
    ' 0 for weekends.
    '
    ' In:
    ' BegDate: First date in range.
    ' EndDate: Last date in range.
    ' Out:
    ' WeekdaysMinusHolidays: Long number of weekdays.
    ' History:
    ' Created 04/11/2000 JPK; Last Modified

    On Local Error GoTo ErrorHandler
    Dim strMsg As String ' for error handling
    Dim db As DAO.Database
    Dim holidays As DAO.Recordset
    Dim d As Long
    Dim Answer As Long
    Dim strCriteria As String

    ' Check for Nulls.
    If IsNull(BookInDate) Or IsNull(BookOutDate) Then
    WeekdaysMinusHolidays = 0
    Exit Function
    End If

    Set db = CurrentDb
    Set holidays = db.OpenRecordset("tblholiday", dbOpenDynaset)
    ' Use this statement for non-linked tables.
    ' holidays.Index = "PrimaryKey"

    Answer = 0

    'pick one:
    For d = BookInDate To BookOutDate 'includes both end points
    'For d = BegDate + 1 To EndDate 'excludes BegDate
    'For d = BegDate To EndDate - 1 'excludes EndDate
    'For d = BegDate + 1 To EndDate - 1 'excludes both end points

    If Weekday(d) <> 1 And Weekday(d) <> 7 Then 'if not a weekend
    ' Use this for non-linked tables.
    ' holidays.Seek "=", d
    ' Remark the following two lines
    ' for non-linked tables.
    strCriteria = "HolidayDate = " & d
    holidays.FindFirst strCriteria
    If holidays.NoMatch Then 'not a holiday
    Answer = Answer + 1
    End If
    End If
    Next d

    holidays.Close
    db.Close

    WeekdaysMinusHolidays = Answer

    ExitProc:
    Exit Function

    ErrorHandler:
    Select Case Err
    Case Else
    strMsg = "Error Information..." & vbCrLf & vbCrLf
    strMsg = strMsg & "Function: WeekdaysMinusHolidays" & vbCrLf
    strMsg = strMsg & "Description: " & Err.Description & vbCrLf
    strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
    MsgBox strMsg, vbInformation, "WeekdaysMinusHolidays"
    Resume ExitProc
    End Select
    End Function

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkDays (A2000 SR1)

    Dave

    I have just cut and pasted your code into a module (A97 W2K) and run it and it works correctly for me!

    29/8/02 to 29/8/02 = 1
    29/8/02 to 30/8/02 = 2
    30/8/02 to 31/8/02 = 1
    31/8/02 to 31/8/02 = 0

    What do you get for these dates?

    Peter

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkDays (A2000 SR1)

    Yes Peter you're correct, the problem doesn't lie in the function, it seems to be simpler.
    This returns 0 "Me.txtTotalDaysOut = [BookOutDate] - [BookInDate]" where it should be 1.
    I have 2 fields as you can see.
    txtWorkingDaysOut (Days less Sat & Sundays)
    txtTotalDaysOut (All Days Out)

    Is their a way to remedy this instead.


    Private Sub ActiveXCtl23_LostFocus()
    On Error GoTo errtrap
    Me.txtWorkingDaysOut.Value = WeekdaysMinusHolidays(Me.BookInDate.Value, Me.BookOutDate.Value)
    Me.txtTotalDaysOut = [BookOutDate] - [BookInDate]
    Exit Sub
    errtrap:
    Attached Images Attached Images

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkDays (A2000 SR1)

    To have the dates inclusive just add 1

    Me.txtTotalDaysOut = [BookOutDate] - [BookInDate] + 1

    In fact the 2 equations do not realy need to go into a module as they could be put directly in the fields control source and they will recalculate whenever a date is changed

    HTH


    Peter

Posting Permissions

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