calculated field using function 1

[Apollo13a]

Hello, I'm trying to use a calculated field in a query to determine the number of days between 2 dates(StartDate and TermDate from tblHistory).My problem is if the second date field is null then I want to use today's date. I placed my code to do the calculation(which I found here on Tek-Tips)in a module. Then I created another function to deal with the null date field.I call the function from a calculated field in my query. I thought I could set the second date to today's date and then send that parameter to the second function. My call works fine if the second date field is populated but I receive an error if it is null. I'm doing this in the query for a report. I think I need a sub to work out the is null problem and then send that to my function but where or how would I do that? Can I send a null parameter to a function? Here's the code for the days calc.

Public Function DiffWeekDays_DaysWorking_Report(datDay1 As Date, datDay2 As Date) As Long
 ' Comments  : Returns the number of business days between two dates
 '             Weekend dates (Saturday and Sunday) are not counted.
 ' Parameters: datDay1 - first (earlier) date/time (subtracted from datDay2)
 '             datDay2 - second (later) date/time
 ' Returns   : Number of whole business days between two dates
 '             (Returns negative days if datDay1 is after datDay2)
 
    Dim lngWeekdays As Long
     Debug.Print
    Dim StartDate As Date, EndDate As Date
    
    'Start Date = Referral
    'End Date= First Sign In Date
     
    lngWeekdays = 0
    StartDate = Format(datDay1, "Short Date")
    EndDate = Format(datDay2, "Short Date")
                     
    Do Until StartDate > EndDate
    Debug.Print
        If Weekday(StartDate) <> 1 And Weekday(StartDate) <> 7 Then
            lngWeekdays = lngWeekdays + 1
        End If
        StartDate = Format(DateAdd("d", 1, StartDate), "Short Date")
    Loop
    
    
    DiffWeekDays_DaysWorking_Report = lngWeekdays

End Function

thanks, jim

 

[PHV]

Have you tried something like this ?
DiffWeekDays_DaysWorking_Report(StartDate, Nz(TermDate, Date()))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Apollo13a]

PHV, Works great.
Thanks, Jim