Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate future business week date (Accesss 2k)

    I need to calcualate 20 business days in the future of a date that is enter nto a field and then set the value into the other field.

    What is the proper syntax.

    Me.Respond_Date = DateAdd("w", ComplaintRecd, 20)
    This gives me calendar days I need 20 business days from the CompliantRec'd date.

    If the date entered = 3/10/04 then the new Respond Date = 4/7/04, however, the above mentioned date returns the value of 3/30/04. What am I doing wrong?

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate future business week date (Accesss 2k)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Hi,

    Access doesn't provide a workdays function so you will need to use VBA to determine the days. You'll find some ways to deal with dates here that maybe of help:

    http://www.mvps.org/access/datetime/index.html

    Cheers
    Tony

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Calculate future business week date (Accesss 2k)

    Here is an example of function to calculate a date, similar to DateAdd function, but taking only workday (business days, i.e., Mon thru Fri) into account:

    Public Function GetNextWorkday(ByVal StartDate As Date, _
    ByVal lngInterval As Long) As Date

    Dim lngWeeks As Long
    Dim lngDays As Long

    If lngInterval = 0 Then
    GetNextWorkday = StartDate
    ElseIf lngInterval > 0 Then
    ' Make sure StartDate is a workday (round down):
    If Weekday(StartDate) = vbSunday Then
    StartDate = StartDate - 2
    ElseIf Weekday(StartDate) = vbSaturday Then
    StartDate = StartDate - 1
    End If

    ' Calculate lngWeeks and lngDays:
    lngWeeks = lngInterval 5 ' Integer division operator
    lngDays = lngInterval - (lngWeeks * 5) ' remainder
    StartDate = StartDate + (lngWeeks * 7)
    ' Account for weekends:
    If (DatePart("w", StartDate) + lngDays) > 6 Then
    StartDate = StartDate + lngDays + 2
    Else
    StartDate = StartDate + lngDays
    End If

    Else ' lngInterval < 0
    lngInterval = lngInterval * -1 ' Make positive & subtract later
    ' Make sure StartDate is a workday (round up):
    If Weekday(StartDate) = vbSunday Then
    StartDate = StartDate + 1
    ElseIf Weekday(StartDate) = vbSaturday Then
    StartDate = StartDate + 2
    End If

    lngWeeks = lngInterval 5
    lngDays = lngInterval - (lngWeeks * 5)
    StartDate = StartDate - (lngWeeks * 7)

    If (DatePart("w", StartDate) - lngDays) < 2 Then
    StartDate = StartDate - lngDays - 2
    Else
    StartDate = StartDate - lngDays
    End If
    End If

    GetNextWorkday = StartDate

    End Function

    You can test results returned by function with this sub:

    Sub TestGetNextWorkday()

    Dim n As Integer
    Dim d As Date

    d = #4/5/2004#
    Debug.Print Format$(d, "mm/dd/yyyy")

    For n = 0 To 20
    Debug.Print n & " " & GetNextWorkday(d, n) & " " & Format$(GetNextWorkday(d, n), "ddd")
    Next n
    For n = 0 To -20 Step -1
    Debug.Print n & " " & GetNextWorkday(d, n) & " " & Format$(GetNextWorkday(d, n), "ddd")
    Next n

    End Sub

    When you run test sub, you should see that only workday dates are returned by function. Note: GetNextWorkday function was based on example published in ACC2000: DateAdd() "w" Interval Does Not Work as Expected, modified somewhat - if you run the code from MSKB article "verbatim", you will get incorrect results for negative intervals, such as when you need date 10 business days previous to start date. Specifically, this line:

    If (DatePart("w", TheDate) - OddDays) > 2 Then

    Should read:

    If (DatePart("w", TheDate) - OddDays) < 2 Then

    to get correct results for negative interval values. (Note: this does appear correctly in the ACC 97 version of same article - see:
    ACC: DateAdd() "w" Interval Does Not Work as Expected

    HTH

Posting Permissions

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