Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    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

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    do your routine
    Else
    getwork = 0
    end if
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank you, here is the zipped file.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #12
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •