DateDiffW()Function

[Debased]

Need Help with DateDiffW()Function

Using your search engine I was able to find the function I need (see code below) however am having a problem - I put the function in a module and then use the function in a text box control on a form and it calculates the number of business days between two dates. For records that have the two dates already entered it works fine and I can edit the dates and it performs the calculation correctly. I get an error however, when I go to add a new record " invalid use of null" I gather it is because there is no date to perform the calculation on??? I don't know how to get around this.

Any suggestions? hope this is enough info, I am new to coding.
Thanks
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

 

[Jon K]

Put this:-

If IsNull(BegDate) Or IsNull(EndDate) Then Exit Function​

as the first line of code in the function.

 

[Debased]

That was so simple I am embarassed! Thanks!