# Thread: Return 0 when dates do not exist (XP 2002)

1. ## Return 0 when dates do not exist (XP 2002)

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

intDays = 0

Do While tmpDate <= EndDate
If Weekday(tmpDate) <> vbSunday And Weekday(tmpDate) <> vbSaturday Then
intDays = intDays + 1
End If
Loop

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

End Function

2. ## Re: Return 0 when dates do not exist (XP 2002)

Tom
Can you test for dates and return zero

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

3. ## Re: Return 0 when dates do not exist (XP 2002)

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.

4. ## Re: Return 0 when dates do not exist (XP 2002)

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)
intDays = 0

Do While tmpDate <= EndDate
If Weekday(tmpDate) <> vbSunday And Weekday(tmpDate) <> vbSaturday Then
intDays = intDays + 1
End If
Loop

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

End Function

5. ## Re: Return 0 when dates do not exist (XP 2002)

Insert the following lines below the declarations:

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

6. ## Re: Return 0 when dates do not exist (XP 2002)

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)
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.

7. ## Re: Return 0 when dates do not exist (XP 2002)

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.

8. ## Re: Return 0 when dates do not exist (XP 2002)

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)

9. ## Re: Return 0 when dates do not exist (XP 2002)

Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

10. ## Re: Return 0 when dates do not exist (XP 2002)

Thank you, here is the zipped file.

11. ## Re: Return 0 when dates do not exist (XP 2002)

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)
<code>
PCR_RequestedApproved: GetWorkDays([dtePCRReq],[dtePCRAppv],True)</code>

12. ## Re: Return 0 when dates do not exist (XP 2002)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•