Hello -- I am fairly new to Access 2003, but I love the program -- and this forum especially for helping me with various things.

I have a database, where in a table there is a DateReceived field, as well as a DateClosed field. If an item is closed then a date is populated in the DateClosed field, and if the item is not closed then the DateClosed field is blank.

In my query, I have a calculdated field to calculate the number of workdays needed to close an item. The expression is: Expr1: CalcWorkdays([DateReceived],[DateClosed]).

The problem I am having and don't know how to solve is that if there isn't a value in the DateClosed field (meaning the item isn't closed), the cacluated field displays a 0. I would like this field to display how many workdays the item has been open. I hope this is possible.

Here is the CalcWorkdays function (I think I found this on this forum actually) -- I also attached this as a .txt file in case it doesn't show up correctly here in the forum:


Function CalcWorkdays(DateReceived, DateClosed) As Integer
Dim LTotalDays As Integer
Dim LSaturdays As Integer
Dim LSundays As Integer
On Error GoTo Err_Execute
CalcWorkdays = 0
If IsDate(DateReceived) And IsDate(DateClosed) Then
If DateClosed <= DateReceived Then
CalcWorkdays = 0
Else
LTotalDays = DateDiff("d", DateReceived - 1, DateClosed)
LSaturdays = DateDiff("ww", DateReceived - 1, DateClosed, 7)
LSundays = DateDiff("ww", DateReceived - 1, DateClosed, 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




Is there anything that can be done to make this work the way I want? Thanks to all in advance for your help!!

Jeremy.

Use the NZ to replace the null (empty) value with the sysdate.

CalcWorkdays([DateReceived],nz([DateClosed],date()))

Or use NZ in your function.