looking for better way to handle null values 1

[leeroi]

I have been using the following function to compute the days difference between dates while subtracting out weekends and holidays. Initially, the code would not run due to a null values problem (sometimes records have missing dates). So I modified the code by adding the following:

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

This may not be the most elegant solution (I'm no coding wiz), but it does allow the code to run. Unfortunately, it also results in a zero as the answer for any computation where a date is missing. Obviously, this can be misleading, since a zero can mean that no days have actually passed, or that one of the dates in the computation is missing. It also screws up things when I'm to compute the average days difference.

My question: how can I modify this function so that it will run, but not return any value at all if a date is missing from the computation?

Here's the entire code below.

Thanks,
Lee


Function WeekdaysMinusHolidays(BegDate, EndDate) As Long

Dim db As Database
Dim holidays As Recordset

Dim d As Long
Dim answer As Long

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

Else

Set db = CurrentDb
Set holidays = db.OpenRecordset("tblholidays&quot
holidays.Index = "primarykey"

answer = 0

'pick one:
'For d = BegDate To EndDate 'includes both end points
For d = BegDate + 1 To EndDate 'excludes BegDate
'For d = BegDate To EndDate - 1 'excludes EndDate
'For d = BegDate + 1 To EndDate - 1 'excludes both end points

If Weekday(d) <> 1 And Weekday(d) <> 7 Then 'if not a weekend
holidays.Seek &quot;=&quot;, d

If holidays.NoMatch Then 'not a holiday
answer = answer + 1
End If
End If

Next d

holidays.close
db.close

WeekdaysMinusHolidays = answer
End If
End Function

 

[Creepers]

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

If BegDate & &quot;&quot; <> &quot;&quot; and EndDate & &quot;&quot; <> &quot;&quot; Then
If IsDate(BegDate) = true and IsDate(EndDate) = true
'Do Action
Else
Exit Function
Endif
Else
Exit Function
Endif

'OR It is faster using the LenB() function

If LenB(BegDate)>0 and LenB(EndDate)>0 Then
If IsDate(BegDate) = true and IsDate(EndDate) = true
'Do Action
Else
Exit Function
Endif
Else
Exit Function
Endif

 

[Creepers]

Sorry, ignore the first two line...They are yours

 

[leeroi]

Sorry, could you clarify exactly how and where this is inserted in my function? When I replace my &quot;IF IsDate&quot; line with either above option, the function won't run and doesn't like the fact that there are two contiguous IF statements.

Lee

 

[Creepers]

I forgot the Then in the nested if.
You should not just insert this right into your code. You will need to place your action second if statement (line 'Do Action)

If BegDate & &quot;&quot; <> &quot;&quot; and EndDate & &quot;&quot; <> &quot;&quot; Then
If IsDate(BegDate) = true and IsDate(EndDate) = true Then
'Do Action
Else
Exit Function
Endif
Else
Exit Function
Endif

'OR It is faster using the LenB() function

If LenB(BegDate)>0 and LenB(EndDate)>0 Then
If IsDate(BegDate) = true and IsDate(EndDate) = true Then
'Do Action
Else
Exit Function
Endif
Else
Exit Function
Endif

 

[leeroi]

Here's my amended function. Is this correct? It runs, but I'm still getting zeros where begdate or enddate hold no data. Sorry to be so dense about this.

Lee

Function WeekdaysMinusHolidays1(BegDate, EndDate) As Long

Dim db As Database
Dim holidays As Recordset

Dim d As Long
Dim answer As Long

If LenB(BegDate) > 0 And LenB(EndDate) > 0 Then
If IsDate(BegDate) = True And IsDate(EndDate) = True Then

Set db = CurrentDb
Set holidays = db.OpenRecordset(&quot;tblholidays&quot
holidays.Index = &quot;primarykey&quot;

answer = 0

'pick one:
'For d = BegDate To EndDate 'includes both end points
For d = BegDate + 1 To EndDate 'excludes BegDate
'For d = BegDate To EndDate - 1 'excludes EndDate
'For d = BegDate + 1 To EndDate - 1 'excludes both end points

If Weekday(d) <> 1 And Weekday(d) <> 7 Then 'if not a weekend
holidays.Seek &quot;=&quot;, d

If holidays.NoMatch Then 'not a holiday
answer = answer + 1
End If
End If

Next d

holidays.close
db.close

WeekdaysMinusHolidays1 = answer

Else
Exit Function
End If
Else
Exit Function
End If

End Function

 

[leeroi]

The solution, once I found it (after stumbling around on the Internet for a couple of hours), was amazingly simple. All I needed to do was to change the function from Long to Variant. Now it works fine!

Lee

 

[FancyPrairie]

You're right, longs can't contain nulls. However, if you want to check for nulls and you know the value can't be &quot;&quot; (vbNullString) then you could modify your code to look something like this:

If (Not IsNull(BegDate)) And (Not IsNull(EndDate)) Then
blah blah blah

Note: Sometimes the Nz function comes in handy (check on-line help)