Nz Function in Business Days HELP!

[vandido626]

I have a query that calculates business days. However if my end date is missing the query has error codes because the date its looking for is null. Can show me were to put the nz function.

here's the module for the business days

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' "Calculating the workdays between Dates"
' Note that this function does account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If

If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays


End Function

and here's the query

SELECT [Reques_ID], Work_days([Date_Received],[To_Director]-1) AS Turnaround_To_Director,
Work_days([To_Director],[From_Director]-1) AS Turnaround_From_Director, Work_days([To_VP],[From_VP]-1) AS Turnaround_From_VP, Work_days([Position_Number_Requested],[Position_Number_Recieved]-1) AS Turnaround_For_Position_Number, Work_days([Date_Received],[Approval_to_mgr]-1) AS Turnaround_EPS, Work_days([Date_Received],[JOIS_Posted_Date]-1) AS Turnaround_To_Posting, Work_days([Approval_to_mgr],[JOIS_Package_Return_Date]-1) AS Turnaround_For_Package_Return
INTO tbl_turnaround
FROM Artifact;

 

[ByteMyzer]

In your function, you might change:

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

...to:

BegDate = CDate(BegDate)
EndDate = CDate(Nz(EndDate, BegDate))

 

[vandido626]

In your function, you might change:

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

...to:

BegDate = CDate(BegDate)
EndDate = CDate(Nz(EndDate, BegDate))

i get an invalid use of null error

here, when i debug
BegDate = CDate(BegDate)

 

[CraigDolphin]

The problem is that you're passing a null beginning date to the function. So, what should the function replace the null date with?

The following would replace the beginning date with the current day's date, and the missing end date with whatever the beginning date is (as Bytemyzer already had).

BegDate = CDate(Nz(BegDate,Date()))
EndDate = CDate(Nz(EndDate, BegDate))

Personally I would test for both dates being non-null before calling the function.

 

[vandido626]

The problem is that you're passing a null beginning date to the function. So, what should the function replace the null date with?

The following would replace the beginning date with the current day's date, and the missing end date with whatever the beginning date is (as Bytemyzer already had).

BegDate = CDate(Nz(BegDate,Date()))
EndDate = CDate(Nz(EndDate, BegDate))

Personally I would test for both dates being non-null before calling the function.

ok, I see it......yea, adding the Date() does fix the error messg...thank you

now, if could ask you one final question.....how do I get the function to return "not available" if one of the two dates is null?

I've tried and i get a mismatch error

BegDate = CDate(Nz(BegDate,"not available"))
EndDate = CDate(Nz(EndDate, "not available"))

 

[CraigDolphin]

It can't return "not available" because you have specified that the function will return an integer.

Function Work_Days(BegDate As Variant, EndDate As Variant) As [b]Integer[/b]

You could handle this in a couple of ways. Redefine your function output to be a string instead of an integer (the 'numbers' will, however, be text and need to be converted back to numbers if you plan to use them in mathematical situations). You will need to remember to test your function output to see if it is 'not available' before doing any math. Look into the CInt function for converting text back into numbers.

Alternatively, before calling the function test to see if both dates are non-null. If they are then call the function etc, if they are not then simply return 'not available' without calling the function for that record. Look into the IIF or Switch functions to test for this kind of thing in a query etc.

incidentally, your code:
BegDate = CDate(Nz(BegDate,"not available"))
EndDate = CDate(Nz(EndDate, "not available"))
is not going to return 'not available' from the function even if you redefine the output to be a string. To make the function return 'not available' you need to have something like:
Work_Days = "Not Available"

 

[AEMB]

I'm working on a similar problem. Is it possible to define the function such that if either BegDate or EndDate doesn't provide a valid date the function would return a null? (I could test each date before calling the function, but that syntax gets a lot messier than if the function could take care of it.)

I'm trying to use the function in a query that is the Record Source for a report. If either date is null the value in the query becomes "#Error" and then the report that uses the value won't run due to "Data type mismatch in criteria expression."

 

[CraigDolphin]

Yes. You would need to define the function as data type 'Variant', and likewise with the two 'dates' you're passing to the function. Variant is the only datatype that allows nulls. However, I understand that there is a performance hit if you use the variant datatype so your queries etc may slow down.

Personally, I think you're better to use the IIF route and only calll the function when both dates are available.

Eg:

MyQueryFieldName: IIF(IsNull(BegDate]),Null,IIF(IsNull(EndDate),Null,Call_Function_Here(BegDate,EndDate)))