Calculating workdays and making null 0

[AnitaPita]

Hi,

I have a query where I'm trying to calculate the number of days between 2 fields. There are some fields which don't have a date/are null. I want to make the result for those a zero. I have tried a few different ways (iif statement, Nz function, IsError etc) nothing seems to work. Below is a sample of one of the formulas I tried to get the total # of days. So Date1 and Date 2 will always have a date, but then I need it to subtract the number of days between Dates 3 and 4. Sometimes Dates 3 and 4 could be null, so I want those to be 0:

WorkDays([Date1],[Date2])-IIf([Date3],[Date4]) Is Null,0,WorkDays([Date3],[Date4]))

Any help would be greatly appreciated!

 

[llkhoutx]

Try:


WorkDays([Date1],[Date2])-WorkDays(NZ([Date3],0),NZ([Date4]))

 

[jdraw]

What is Workdays? User defined function??
I found this at techonthenet - may be helpful.

Function CalcWorkdays(StartDate as Date, EndDate as Date) As Integer

   Dim LTotalDays As Integer
   Dim LSaturdays As Integer
   Dim LSundays As Integer
   
   On Error GoTo Err_Execute
   
   CalcWorkdays = 0
   
   If IsDate(StartDate) And IsDate(EndDate) Then
      If EndDate <= StartDate Then
         CalcWorkdays = 0
      Else
         LTotalDays = DateDiff("d", StartDate - 1, EndDate)
         LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
         LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)
         
         'Workdays is the elapsed days excluding Saturdays and Sundays
         CalcWorkdays = LTotalDays - LSaturdays - LSundays
         
      End If
     
   End If
   
   Exit Function
   
Err_Execute:
   'If error occurs, return 0
   CalcWorkdays = 0
   
End Function