1. ## 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.

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. ## 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. ## 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

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
•