error message with Date calculation

[krberube]

Hi all,
I am calculating business days between 2 dates using a function I found here
It works great, as long as both dates are NOT NULL. In my form, the record may not have a "received date" as its not here yet. When i open the form I get a message "invalid use of null" on the SELECT CASE WEEKDAY(BEGDATE) line. Any ideas how to handle the null?

Thanks
Kevin

Here is the function:

Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function

 

[krberube]

It looks like i fixed it.
I placed the following 2 lines before the line that is giving me the error.

If IsDate(dStartDate) = False Then Exit Function
If IsDate(dEndDate) = False Then Exit Function

Thanks

 

[krberube]

Its almost there, but not quite. If ENDDate is Null I need the function to make the result (DATEDIFFW) = 0, to show zero in the control box.

Any suggestions? Below is the code as I have it now.

Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If IsDate(BegDate) = False Then Exit Function
If IsDate(EndDate) = False Then Exit Function

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function

 

[Jon K]

If IsDate(EndDate) = False Then 
    DateDiffW=0
    Exit Function
End If

 

[krberube]

Thanks JonK!