Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate Work Days (A2K/SR-3)

    I know this has been covered before, specifically in post <post#=274209>post 274209</post#>. I have also taken code from The Access Web, which was built from code found in The Microsoft Developer Network, and has another method for calculating days by excluding holidays. The problem is, the code is quite slow, since it increments through a table of holidays and counts those dates.

    I thought it would be quicker to create a recordset of the non-working days, filter it to include only non-working days within the date range questioned, then count the records.

    Here is my code:

    <pre>Public Function CountHolidays(rstNonWorkDays As Recordset, _
    dtmStart As Date, dtmEnd As Date) As Long

    ' Count holidays between two end dates.

    ' Required by:
    ' dhCountWorkdays

    Dim lngCount As Long
    Dim strSQL As String

    On Error GoTo HandleErr

    strSQL = "[NonWorkDate] BETWEEN #" & dtmStart & "# AND #" & dtmEnd & "#"
    rstNonWorkDays.Filter = strSQL
    lngCount = rstNonWorkDays.OpenRecordset.RecordCount

    ExitHere:
    CountHolidays = lngCount
    Exit Function

    HandleErr:
    ' No matter what the error, just
    ' return without complaining.
    ' The worst that could happen is that the code
    ' include a holiday as a real day, even if
    ' it's in the table.
    Resume ExitHere
    End Function</pre>



    In my non-working day table, tblNonWorkDays, there are 7 non-scheduled days for March in field [NonWorkDate]. CountHolidays(CurrentDb.OpenRecordset("SELECT [NonWorkDate] FROM tblNonWorkDays",dbOpenSnapshot),#3/01/05#, #3/31/05#), however, I only ever returns 1 (but at least I get it fast!). Any suggestions?

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

    Re: Calculate Work Days (A2K/SR-3)

    When you open a DAO recordset, RecordCount does not return the exact record count, it only indicates whether there are records (RecordCount = 1) or none (RecordCount = 0). For efficiency reasons, DAO doesn't read all records when the recordset is opened, so the exact record count is not known yet. You must move to the last record to force DAO to calculate the correct count.

    <code>With rstNonWorkDays.OpenRecordset
    .MoveLast
    lngCount = .RecordCount
    End With</code>

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate Work Days (A2K/SR-3)

    It worked perfectly, Hans. Thank you.

    Now, however, I'm trying to use the function from the Expression builder to calculate the workdays in a query. As long as I don't try to pass the optional recordset, the query works. If I try to pass the name of the table containing the non-working days as a recordset, I get an ERROR#. How do I pass a recordset in a query, if at all possible? Must I resort to code?

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

    Re: Calculate Work Days (A2K/SR-3)

    The argument to your function is a recordset object, not a string containing the name of a table. Where does the recordset come from if you run a query? Do you create it when the database is opened, or ...?

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate Work Days (A2K/SR-3)

    In most instances, Hans, I'll use a table called tblNonWorkDays. In some instances, though, I'll use the same functions from code, creating an instance of a recordset using CurrentDb.OpenRecordset("SELECT [NonWorkDate] FROM tblNonWorkdays",dbOpenSnapshot). This previous statement doesn't work in the Expression builder to build a query, however. I'm sure there is an equivalent statement I can use, but I can't find it in Help.

    When writing these functions, I assumed the original code writers had good reason to use recordsets rather than tables, so I stayed with recordsets. Would it be better to use a table? If so, however, I still don't know how to pass that in SQL....

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

    Re: Calculate Work Days (A2K/SR-3)

    It is very well possible to use recordsets in a function, but I don't think I really understand what you are doing here. Could you try to explain it in simple terms?

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate Work Days (A2K/SR-3)

    Using the following function:

    <pre> <font color=blue>Public Function dhCountWorkdays(ByVal dtmStart As Date, ByVal dtmEnd As Date, _
    Optional rstNonWorkDays As Recordset = Nothing, _
    Optional strField As String = "") As Integer

    ' Count the business days (not counting weekends/holidays) in
    ' a given date range.

    ' Modified from code in
    ' "Visual Basic Language Developer's Handbook"
    ' by Ken Getz and Mike Gilbert
    ' Copyright 2000; Sybex, Inc. All rights reserved.

    ' Requires:
    ' SkipHolidays
    ' CountHolidays
    ' IsWeekend

    ' In:
    ' dtmStart:
    ' Date specifying the start of the range (inclusive)
    ' dtmEnd:
    ' Date specifying the end of the range (inclusive)
    ' (dates will be swapped if out of order)
    ' rstNonWorkDays (Optional):
    ' Recordset containing holiday dates.
    ' Out:
    ' Return Value:
    ' Number of working days (not counting weekends and optionally, holidays)
    ' in the specified range.
    ' Example:
    ' Debug.Print dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, CurrentDb.OpenRecordset
    ' ("SELECT [NonWorkDate] FROM tblNonWorkDays",dbOpenSnapshot))
    ' returns 2, because 7/2/2000 is Sunday, 7/4/2000 is a holiday,
    ' leaving 7/3 and 7/5 as workdays.

    Dim intDays As Integer
    Dim dtmTemp As Date
    Dim intSubtract As Integer
    Dim SevenDayWeek As Boolean

    SevenDayWeek = CBool(DLookup("[7DayWeek]", "tblAssemblyPlant"))
    intSubtract = 0

    ' Swap the dates if necessary.>
    If dtmEnd < dtmStart Then
    dtmTemp = dtmStart
    dtmStart = dtmEnd
    dtmEnd = dtmTemp
    End If

    ' Get the start and end dates to be weekdays.
    dtmStart = SkipHolidays(rstNonWorkDays, strField, dtmStart, 1)
    dtmEnd = SkipHolidays(rstNonWorkDays, strField, dtmEnd, -1)
    If dtmStart > dtmEnd Then
    ' Sorry, no Workdays to be had. Just return 0.
    dhCountWorkdays = 0
    Else
    intDays = dtmEnd - dtmStart + 1

    ' Subtract off weekend days. Do this by figuring out how
    ' many calendar weeks there are between the dates, and
    ' multiplying the difference by two (because there are two
    ' weekend days for each week--but only if not working a 7
    ' day work week). That is, if the difference
    ' is 0, the two days are in the same week. If the
    ' difference is 1, then we have two weekend days.

    If Not SevenDayWeek Then
    intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)
    End If

    ' The answer to our quest is all the weekdays, minus any
    ' holidays found in the table, if supplied.
    If Not IsMissing(rstNonWorkDays) Then intSubtract = intSubtract + _
    CountHolidays(rstNonWorkDays, strField, dtmStart, dtmEnd)

    dhCountWorkdays = intDays - intSubtract
    End If

    End Function</font color=blue></pre>



    I can easily count workdays between two dates as long as I use code, e.g.:

    <pre><font color=blue>Me.txtDaysOverdue = dhCountWorkdays(Me.txtDueDate, Date(), _
    CurrentDb.OpenRecordset("SELECT [NonWorkDate' FROM tblNonWorkdays",dbOpenSnapshot), _
    "[NonWorkDate]")</font color=blue></pre>


    However, if I try to use the function in a reqular query:

    <pre><font color=448800>SELECT tblBlueCardInitiate.RespGroup, tblBlueCardInitiate.DueDate, _
    dhCountWorkdays([DueDate],Date(),"tblNonWorkDays","[NonWorkDate]") _
    AS WorkDaysOverdue
    FROM tblBlueCardInitiate;</font color=448800></pre>


    WorkdaysOverdue returns #Error. I'm certain this is because I can't send a recordset to the function as a table name in quotes, but I don't know how to send it to the function in SQL and Help hasn't been helpful. Does this better clarify what I'm trying to accomplish?

    I don't mind using code if necessary, but I'll be re-using the query, if I can get it to work, so I'd like to make this work.

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

    Re: Calculate Work Days (A2K/SR-3)

    You could pass the table name as an argument, and open a recordset on the table in the function:

    Public Function dhCountWorkdays(ByVal dtmStart As Date, ByVal dtmEnd As Date, _
    Optional strTable As String = "", _
    Optional strField As String = "") As Integer
    ...
    Dim rstNonWorkDays As DAO.Recordset
    If Not (strTable = "") Then
    Set rstNonWorkDays = CurrentDb.OpenRecordset(strTable)
    End If
    ...

    You need to check further on whether rstNonWorkDays is not Nothing.

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate Work Days (A2K/SR-3)

    I expected nothing less than a solution that worked correctly, Hans, and again, you provided it. After a little code surgery, I changed all references to the recordset in the public functions to a string that references a table name. I created a private function to create a recordset from the name of the table and the name of the field containing the non-workday. Now I can reference these functions either in code or in a regular query. Thank you.

    The only problem is that although this code executes faster than the code I found in the sites above, it is still rather slow. Regardless, it does what I need. Thanks again!

Posting Permissions

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