Return 0 when dates do not exist (XP 2002)

Topic

New Reply

I have a function that returns the number of Business Days elapsed between two dates minus Holidays that occur on business days.
I want to be able to return zero days when [StartDate] and or [EndDate] do not exist.

Here is the primary Function that I am using.
I have not included the CountHolidays Function as the message might be too long.

My end product will be a report that has 3 expressions for different Business Days counts.

Thus, most rows will not have all the date fields inputted as each row (project) is in different stages of completion.

Function GetWorkDays(ByVal StartDate As Date, ByVal EndDate As Date, ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

lngWeeks = DateDiff(“w”, StartDate, EndDate)
tmpD

ate = DateAdd(“ww”, lngWeeks, StartDate)
intDays = 0

Do While tmpDate <= EndDate
If Weekday(tmpDate) vbSunday And Weekday(tmpDate) vbSaturday Then
intDays = intDays + 1
End If
tmpDate = DateAdd(“d”, 1, tmpDate)
Loop

If CountHolidays = True Then
GetWorkDays = lngWeeks * 5 + intDays – GetHolidayCount(StartDate, EndDate)
Else
GetWorkDays = lngWeeks * 5 + intDays
End If

End Function

Reply | Quote

Replies

Tom
Can you test for dates and return zero

If IsDate(StartDate ) _
And IsDate(EndDate ) Then
do your routine
Else
getwork = 0
end if

Reply | Quote

In the definition of your function, StartDate and EndDate are declared as Date values, so the function will cause an error if StartDate or EndDate is null. Instead, declare them as Variant values. You can then use IsNull and IsDate to test the values.

Reply | Quote

Hans, I have declared the values as Variants and have tried many permutations of code to get this function to return zero (0) days when either or both the StartDate & EndDate are missing.
I need your help as I have been banging my head for some time. I also tried setting all variables to Variant type. I have tried IsNull() and IsDate() with If statements.

Function GetWorkDays(ByVal StartDate As Variant, _
ByVal EndDate As Variant, _
ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

lngWeeks = DateDiff(“w”, StartDate, EndDate)
tmpDate = DateAdd(“ww”, lngWeeks, StartDate)
intDays = 0

Do While tmpDate <= EndDate
If Weekday(tmpDate) vbSunday And Weekday(tmpDate) vbSaturday Then
intDays = intDays + 1
End If
tmpDate = DateAdd(“d”, 1, tmpDate)
Loop

If CountHolidays = True Then
GetWorkDays = lngWeeks * 5 + intDays – GetHolidayCount(StartDate, EndDate)
Else
GetWorkDays = lngWeeks * 5 + intDays
End If

End Function

Reply | Quote

Insert the following lines below the declarations:

If IsNull(StartDate) Or IsNull(EndDate) Then
GetWorkDays = 0
Exit Function
End If

Reply | Quote

Thank you for your response. I inserted the code as follows. I am still receiving the #Error when StartDate or EndDate do not have entries in the table.
I tried inserting the code in different places. It is interesting that the code you used is identical to some code I tried earlier except for the line, EXIT FUNCTION.

Function GetWorkDays(ByVal StartDate As Variant, _
ByVal EndDate As Variant, _
ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

lngWeeks = DateDiff(“w”, StartDate, EndDate)
tmpDate = DateAdd(“ww”, lngWeeks, StartDate)
intDays = 0

If IsNull(StartDate) Or IsNull(EndDate) Then
GetWorkDays = 0
Exit Function
End If

Do While tmpDate <= EndDate
'The code continues, see orginal posting for the whole code.

Reply | Quote

As I wrote in my previous reply, you should insert the lines below the declarations, i.e. below the line

Dim intDays As Integer

By inserting them lower down, you made them useless.

Reply | Quote

I inserted the lines as instructed and I still get #Error.

Function GetWorkDays(ByVal StartDate As Variant, _
ByVal EndDate As Variant, _
ByRef CountHolidays As Boolean) As Long

Dim lngWeeks As Long
Dim tmpDate As Date
Dim intDays As Integer

If IsNull(StartDate) Or IsNull(EndDate) Then
GetWorkDays = 0
Exit Function
End If

lngWeeks = DateDiff(“w”, StartDate, EndDate)

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Thank you, here is the zipped file.

Reply | Quote

You call the function incorrectly. The third argument CountHolidays is a Boolean, i.e. you should specify True (to take holidays into account) or False (to ignore holidays). You have specified an expression such as GetHolidayCount([dtePCRReq];[dtePCRAppv]) as third argument; this is not a Boolean.

Try (for example)

PCR_RequestedApproved: GetWorkDays([dtePCRReq],[dtePCRAppv],True)

Reply | Quote

Yahoo! That did the trick. Thank you very much. I was looking in the wrong place.

Reply | Quote