Code help

[jamphan]

I have the following code in a module that works perfectly is the dtmRequestCompletion field is filled in. In my case it will not always be filled in. How can I get this code to return a null value if the field does not have a value and also carry through with the other code for records that do have this filled in. I am using this code in a query to get the business days between the 2 dates. Thanks for any help!

Function DateDiffW(dtmDateRequested, dtmRequestCompletion)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

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

 

[Crilen007]

Function DateDiffW(dtmDateRequested, dtmRequestCompletion)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If dtmDateRequested is null or dtmRequestCompletion is null then
    datediffw = 0
else
If dtmDateRequested > dtmRequestCompletion Then
DateDiffW = 0
Else
Select Case WeekDay(dtmDateRequested)
Case SUNDAY: dtmDateRequested = dtmDateRequested + 1
Case SATURDAY: dtmDateRequested = dtmDateRequested + 2
End Select
Select Case WeekDay(dtmRequestCompletion)
Case SUNDAY: dtmRequestCompletion = dtmRequestCompletion - 2
Case SATURDAY: dtmRequestCompletion = dtmRequestCompletion - 1
End Select
NumWeeks = DateDiff("ww", dtmDateRequested, dtmRequestCompletion)
DateDiffW = NumWeeks * 5 + WeekDay(dtmRequestCompletion) - WeekDay(dtmDateRequested)
End If
end if
End Function

 

[jamphan]

Function DateDiffW(dtmDateRequested, dtmRequestCompletion)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
I took your code that you provided and modified it slightly. I am getting a Compile Error of "Else Without If". What does this mean? I was getting it with your code as well.

If dtmDateRequested Is Null Then DateDiffW = Null
Else
Select Case WeekDay(dtmDateRequested)
Case SUNDAY: dtmDateRequested = dtmDateRequested + 1
Case SATURDAY: dtmDateRequested = dtmDateRequested + 2
End Select
Select Case WeekDay(dtmRequestCompletion)
Case SUNDAY: dtmRequestCompletion = dtmRequestCompletion - 2
Case SATURDAY: dtmRequestCompletion = dtmRequestCompletion - 1
End Select
NumWeeks = DateDiff("ww", dtmDateRequested, dtmRequestCompletion)
DateDiffW = NumWeeks * 5 + WeekDay(dtmRequestCompletion) - WeekDay(dtmDateRequested)
End If
End Function

 

[jamphan]

Sorry for the last post. I typed it incorrectly. I am getting a compile error of "Else Without If". I did modify the code you provided slightly however I was getting the same error with your code.

Function DateDiffW(dtmDateRequested, dtmRequestCompletion)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If dtmDateRequested Is Null Then DateDiffW = Null
Else
Select Case WeekDay(dtmDateRequested)
Case SUNDAY: dtmDateRequested = dtmDateRequested + 1
Case SATURDAY: dtmDateRequested = dtmDateRequested + 2
End Select
Select Case WeekDay(dtmRequestCompletion)
Case SUNDAY: dtmRequestCompletion = dtmRequestCompletion - 2
Case SATURDAY: dtmRequestCompletion = dtmRequestCompletion - 1
End Select
NumWeeks = DateDiff("ww", dtmDateRequested, dtmRequestCompletion)
DateDiffW = NumWeeks * 5 + WeekDay(dtmRequestCompletion) - WeekDay(dtmDateRequested)
End If
End Function