Results 1 to 3 of 3

Thread: Workdays (2000)

  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workdays (2000)

    I'm using the following function (direct from the Microsoft Knowledge Base) to figure out workdays. However, when I use the function in a query with one field each for the start date and end date and one field using the function to calculate the work days, I get a run time error when I start entering the first date that says "invalid use of null." It appears to be trying to calculate as I'm entering. Is there a way around this?

    Thanks,
    Eric

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

    ' Note that this function does not account for holidays.

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

    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    WholeWeeks = DateDiff("w", BegDate, EndDate)
    DateCnt = DateAdd("ww", WholeWeeks, BegDate)
    EndDays = 0
    Do While DateCnt <= EndDate
    If Format(DateCnt, "ddd") <> "Sun" And _
    Format(DateCnt, "ddd") <> "Sat" Then
    EndDays = EndDays + 1
    End If
    DateCnt = DateAdd("d", 1, DateCnt)
    Loop
    Work_Days = WholeWeeks * 5 + EndDays

    End Function

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays (2000)

    Modify your code to check if the passed variants are dates<pre>Dim WholeWeeks As Variant
    Dim DateCnt As Variant
    Dim EndDays As Integer
    If Not IsDate(BegDate) Or Not IsDate(EndDate) Then
    Work_Days = 0
    Exit Function
    End If
    BegDate = DateValue(BegDate)
    EndDate = DateValue(EndDate)
    ...</pre>

    Francois

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workdays (2000)

    Thank you very much, that works.

Posting Permissions

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