NetworkDays returning #Error

[blues763]

Hi there,

I'm using the following function to calculate date diff in network days (excluding weekends)

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbMonday) < 6 Then

NetWorkdays = NetWorkdays + 1
End If

Next i
End Function

----------------

so when using this function in a query to get number of networkdays between 2 date columns ...it works fine but throws a #Error where there is blank entries in either of 2 date columns...

I need to find a way to display Null instead of #Error...

I have tried this expression but no luck...
IIf(IsError(NetWorkdays([Date1],[Date2])),"",NetWorkdays([Date1],[Date2]))

 

[pr2-eugin]

You can modify your function as,

Public Function NetWorkdays([COLOR=Red]dteStart, dteEnd[/COLOR]) As Integer
    Dim intGrossDays As Integer
    Dim dteCurrDate As Date
    Dim i As Integer
    
[COLOR=Red]    If IsNull(dteStart) Or IsNull(dteEnd) Then 
        NetWorkdays = 0
        Exit Function
    End If[/COLOR]
    
    intGrossDays = DateDiff("d", dteStart, dteEnd)
    
    For i = 0 To intGrossDays
        dteCurrDate = dteStart + i
        If Weekday(dteCurrDate, vbMonday) < 6 Then
            NetWorkdays = NetWorkdays + 1
        End If
    Next i
End Function

Then simply call without any IIF.

 

[blues763]

Hi

Tried using above but it comes back with a "Invalid use of Null" error
then kill my database where my forms stops working (File Not found)

luckily I have a back up copy

 

[pr2-eugin]

I am very sorry, I used the wrong function, should have been IsNull. Try that again.

 

[blues763]

Perfect.... Thanks a lot
I did think that but I guess a new-bee is always scared..

 

[RainLover]

Hi

Tried using above but it comes back with a "Invalid use of Null" error
then kill my database where my forms stops working (File Not found)

luckily I have a back up copy

You should always work on a Production copy not a Working copy. Also you should back up every 30 Minutes or so. Perhaps more often.

 

[RainLover]

With this nett workdays, there are copies of this on the web written by various people. Did you do a Google and not find something that sorted your need. If you want more help I will find a copy for you. Just sing out.

 

[vbaInet]

One last thing, with dates always use the IsDate() function because it covers all basis, i.e. Nulls and invalid dates.