Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working Days (Access 97)

    Hi

    I am using the following code for working days (including holidays), which was posted here I believe. However, the DCount function is returning zero when in fact it should be returning two. Even when modifying it to select a specific holiday date ie Holiday = #4/18/2003# it still returns zero.

    Here is the code:

    Public Function gfWorkingDays(Beginning As Date, Ending As Date) As Integer
    Dim intDays As Integer

    If Beginning > Ending Then
    gfWorkingDays = 0
    Exit Function
    End If

    intDays = DateDiff("d", Beginning, Ending) + 1
    intDays = intDays - 2 * DateDiff("ww", Beginning, Ending)
    intDays = intDays + (WeekDay(Beginning) = vbSunday)
    intDays = intDays + (WeekDay(Ending) = vbSaturday)
    intDays = intDays - DCount("*", "tblHolidays", _
    "Holiday BETWEEN " & Beginning & " AND " & Ending)
    gfWorkingDays = intDays

    end function

    The tblholidays table has one field Holiday set up as a medium date. There are two entries 18 Apr 2003 and 21 Apr 2003. The beginning and ending dates are 12 Apr 2003 and 16 May 2003.

    At a guess the problem is a date format issue (I tried using '#" around the date fields, but this made no difference), but I can't seem to resolve it. Any help much appreciated.

    Regards
    WTH

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Working Days (Access 97)

    Recommend modify DCount expression to include the date delimiters in Where argument. Example:

    <pre>DCount("*", "Orders", "[OrderDate] Between #" & StartDt & "# And #" & EndDt & "#")</pre>


    Testing this, if delimiters were excluded in DCount, function returned 0. When delimiters included as illustrated, function returned correct number of records that matched Where criteria.

    HTH

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Working Days (Access 97)

    The code:
    "Holiday BETWEEN " & Beginning & " AND " & Ending)

    should read:
    "Holiday BETWEEN " & Format(Beginning,"mm/dd/yy") & " AND " & Format(Ending,"mm/dd/yy"))

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 97)

    Hi

    Thanks for the suggestions. I had already tried the first and it failed, and unfortunately, the 2nd one failed as well (I tried the "dd/mm/yy" format as well, with no success). It may be helpful if I a sample DB showing the offending problem. In the form use the left-hand calculation to display the problem.

    I wonder whether it may be a system date problem, if it works ok for you.

    Regards

    WTH
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Working Days (Access 97)

    Try this:

    "Holiday BETWEEN #" & Format(Beginning, "mm/dd/yy") & "# AND #" & Format(Ending, "mm/dd/yy") & "#"

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Working Days (Access 97)

    Use a combination of the suggestions by MarkD and Patt: the # to tell SQL that this is a date, and the Format to make sure it is in US format (required by SQL):

    DCount("*", "tblHolidays", _
    "Holiday BETWEEN #" & Format(Beginning, "mm/dd/yyyy") & "# AND #" & Format(Ending, "mm/dd/yyyy") & "#")

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 97)

    Hi

    You beauty, the last suggestion works! (I wish it was not necessary to go to such lengths to explicitly state a date)

    Thanks to all three for your assistance.

    Regards
    WTH

Posting Permissions

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