NetworkingDays

[tomnsd]

I have a function to return the networking days. It seems to work fine, except if there is a date missing it returns a #error. I would like it just to leave the cell blank. Can someone help?

Option Compare Database

Option Explicit


Public Function Workdays(dteStart As Date, dteEnd As Date) As Long
    Dim lngDate As Long
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
        
     If IsNull(dteStart) Or IsNull(dteEnd) Then
     GoTo Exitpoint
     End If
     
     Set dbs = CurrentDb
     Set rst = dbs.OpenRecordset("Holidays", dbOpenSnapshot)
     
   ' NetWorkdays = -1
    ' Check for valid dates.
    If IsDate(dteStart) And IsDate(dteEnd) Then
        ' Strip off any fractional days and just use whole days.
        For lngDate = Int(dteStart) To Int(dteEnd)
        
            If Weekday(lngDate, vbMonday) < 6 Then
                   rst.FindFirst "[Holiday] = #" & Format(lngDate, "mm\/dd\/yyyy") & "#"
                   If rst.NoMatch Then
                   Workdays = Workdays + 1
                   End If
              
            End If
        Next lngDate
    End If
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing


Exitpoint:
   
    Exit Function


End Function

[June7]

Are you calling function in a query?

Show the expression that calls the function.

[tomnsd]

Yes. Workdays(3/12/2013,3/15/2013)

[June7]

If you are calling in a query I presume there are actually field names where you show date values in that expression. I was looking for your field names but I will use mine instead. Try:

IIf(IsNull(StartDate) Or IsNull(EndDate), Null, Workdays(Nz(StartDate,#1/1/2000#),Nz(EndDate,#1/1/2000#)))

[tomnsd]

Thank you.