Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date functions available in Access (Access 2003)

    Is there a way to modify the dateadd function so that I can use it to add days, if the date falls on a Saturday or Sunday?

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

    Re: date functions available in Access (Access 2003)

    You'll find a whole series of functions that handle workday arithmetic on the Access web: Date/Time: Doing WorkDay Math in VBA. If you copy those functions into a standard module in your database, you can use the functions in VBA code but also in expressions in queries, forms and reports.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date functions available in Access (Access 2003)

    Hans,

    I have looked over the functions in the link that you provided, and do not see a specific one that would fit my need.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date functions available in Access (Access 2003)

    Ok, I have had some time to think about this. I had taken a some code from another post to help calculate workdays. When I looked at it for the one hundredth time, I finally saw were the code rounded down days to the previous workday instead of up to the next workday. I have made the modifecation and it works like it should. Modified code:

    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 up):
    If Weekday(StartDate) = vbSunday Then
    StartDate = StartDate + 1
    ElseIf Weekday(StartDate) = vbSaturday Then
    StartDate = StartDate + 2
    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

Posting Permissions

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