I know this is a 'dead horse' here however, I've searched this site and still can't get it right...
I have a field named [court_date] and a field named [serve_by]. The [served_by] date has to be 7 working days before the [court date].
I copied and pasted the following function from this site:
- '**********************************************************
-
'Declarations section of the module
-
'**********************************************************
-
-
Option Explicit
-
-
'==========================================================
-
' The DateAddW() function provides a workday substitute
-
' for DateAdd("w", number, date). This function performs
-
' error checking and ignores fractional Interval values.
-
'==========================================================
-
Function DateAddW (ByVal TheDate, ByVal Interval)
-
-
Dim Weeks As Long, OddDays As Long, Temp As String
-
-
If VarType(TheDate) <> 7 Or VarType(Interval) < 2 Or _
-
VarType(Interval) > 5 Then
-
DateAddW = TheDate
-
ElseIf Interval = 0 Then
-
DateAddW = TheDate
-
ElseIf Interval > 0 Then
-
Interval = Int(Interval)
-
-
' Make sure TheDate is a workday (round down).
-
-
Temp = Format(TheDate, "ddd")
-
If Temp = "Sun" Then
-
TheDate = TheDate - 2
-
ElseIf Temp = "Sat" Then
-
TheDate = TheDate - 1
-
End If
-
-
' Calculate Weeks and OddDays.
-
-
Weeks = Int(Interval / 5)
-
OddDays = Interval - (Weeks * 5)
-
TheDate = TheDate + (Weeks * 7)
-
-
' Take OddDays weekend into account.
-
-
If (DatePart("w", TheDate) + OddDays) > 6 Then
-
TheDate = TheDate + OddDays + 2
-
Else
-
TheDate = TheDate + OddDays
-
End If
-
-
DateAddW = TheDate
-
Else ' Interval is < 0
-
Interval = Int(-Interval) ' Make positive & subtract later.
-
-
' Make sure TheDate is a workday (round up).
-
-
Temp = Format(TheDate, "ddd")
-
If Temp = "Sun" Then
-
TheDate = TheDate + 1
-
ElseIf Temp = "Sat" Then
-
TheDate = TheDate + 2
-
End If
-
-
' Calculate Weeks and OddDays.
-
-
Weeks = Int(Interval / 5)
-
OddDays = Interval - (Weeks * 5)
-
TheDate = TheDate - (Weeks * 7)
-
-
' Take OddDays weekend into account.
-
-
If (DatePart("w", TheDate) - OddDays) > 2 Then
-
TheDate = TheDate - OddDays - 2
-
Else
-
TheDate = TheDate - OddDays
-
End If
-
-
DateAddW = TheDate
-
End If
-
-
End Function
However, I can't get it to work.
When I put in the code:
- Serve_by= DateAddW ("d", -7, [court_date])
I get "Complie Error: Wrong Number of Arguments or Invalid Property Assignments"
When I put in the code:
- Serve_by= DateAddW(-7, [Court_date])
It returns "01/01/2010"
What am I doing Wrong?
