Results 1 to 9 of 9
  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 (Again) (Access 2002)

    Hi

    This is a follow up from the <post#=238677>post 238677</post#>. I have another go at it based on seeing some code in a Crystal Report forum. Also received some help from this forum to complete it. The advantage of this method is it does not require storing of dates in a table, thus slowing down the processing time. Rather the dates are hard coded into a module. Unfortunately, I can't resolve the problem of Easter being such a moveable feast (no pun intended).

    The advantage of this system is you do not need to store dates etc for a number of years.

    In the form it gives two options - 1 Number of working days between two dates and 2 The end date based on number of working days and the start date.

    I would welcome advice from the Forum on any bugs (meaning it gives the incorrect answer) or as I tend to over engineer things some clues on how to make it more efficient.

    Thanks & Regards

    Warren

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

    Re: Working Days (Again) (Access 2002)

    In the On Click event procedure for the command buttons on the form, you perform some validity checks, but continue if they fail. You should exit the procedure, for example

    If IsNull(Me.TxtStartDate) Then
    MsgBox "Start Date Blank"
    Me.TxtStartDate.SetFocus
    Exit Sub
    End If

    and similar for the other tests.

    I would prefer using a table for the holidays, it's much easier to maintain than hard-coded arrays. In the code you have, you must change two arrays (holidays and holidays1) if you want to edite, add or remove a holiday. Editing the code requires that you have exclusive access to the database; editing records in a table doesn't, and is less error-prone.

  3. #3
    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 (Again) (Access 2002)

    Hi Hans

    Thanks for the comments and oops over missing the exit sub part of the test if statement.

    I agree there is the drawback of editing the hard code, but I took the view that the set holidays do not change very often. The problem with maintaining a table of holiday dates is it may not cover the range of dates for working day calculations or someone forgets to update it with the next 5 years of holidays. I guess it is a matter of personal perference and it gives people an alternative to maintaining a table of holidays.

    Regards
    WTH

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

    Re: Working Days (Again) (Access 2002)

    With your holiday table, you could have the switchboard see if there are any dates set for the current year, and if not, throw up a dialog box to let the user know they must input the current years holidays.

    Alternatively, I wonder if you could keep a table of the holidays that occur on a particular day in the month (1st day, or 3rd Monday). Easter could be a problem, but then what formula do they currently use to calculate easter. This table would not have to change.
    This table could be used to generate a this years holidays table, which could then be used for the queries.

  5. #5
    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 (Again) (Access 2002)

    Hi Pat

    The post referenced above looked at storing a table for 1st Monday of the week etc, but performance wise it was not great. However, I could look at it again using some of the code I used for the updated version in this post, which may speed things up. As you say Easter is always a problem and the only way is to store in a table.

    Use of a prompt at the switchboard or when running the report is a possibility, but I was trying to remove the need for this as users always seem to have the ability to put in the wrong data or don't do what they are told to do.

    Regards
    WTH

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

    Re: Working Days (Again) (Access 2002)

    I looked back over that thread you mentioned and there was a calculation for Easter provided by EnvoyPV in <post#=239447>post 239447</post#>, however it did not mention what date it calculated (eg Easter Monday).

  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 (Again) (Access 2002)

    Hi Pat

    Well spotted, I forgot all about that.

    The date it gives is Easter Sunday. I tested this using the following code:

    Function Easter(Easteryear As Integer) As String
    Dim a
    Dim b
    Dim c
    Dim d
    Dim e
    Dim f
    Dim g
    Dim h
    Dim i
    Dim k
    Dim l
    Dim m
    Dim n
    Dim p
    Dim result
    a = Easteryear Mod 19
    b = Fix(Easteryear / 100)
    c = Easteryear Mod 100
    d = Fix(b / 4)
    e = b Mod 4
    f = Fix((b + 8) / 25)
    g = Fix((b - f + 1) / 3)
    h = (19 * a + b - d - g + 15) Mod 30
    i = Fix(c / 4)
    k = c Mod 4
    l = (32 + 2 * e + 2 * i - h - k) Mod 7
    m = Fix((a + 11 * h + 22 * l) / 451)
    n = Fix((h + l - 7 * m + 114) / 31) ' [3=March, 4=April]
    p = (h + l - 7 * m + 114) Mod 31
    result = p + 1 '(date in Easter Month)
    Easter = Format(DateSerial(Easteryear, n, result), "dd MMMM yyyy")
    End Function

    Private Sub test()
    Dim t
    t = Easter(2005)
    MsgBox t
    End Sub

    So knowing that I should be able to build in the holidays for Easter Friday and Monday. Many thanks for the pointer in the right direction.

    Regards
    WTH

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

    Re: Working Days (Again) (Access 2002)

    <post#=239832>post 239832</post#> provides a database that uses the calc to populate a table with Easter Friday and Easter Monday.
    I would still use a table to record all these dates as it gives you the opportunity to just add/delete table entries rather than changing code, it does not need a developer to change the dates.

    Also it would be interesting to time a query that joins (inner join) the tblDates table with the data source you need for a report. Test this against the arrays in memory when testing for a date in a query.

  9. #9
    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 (Again) (Access 2002)

    Hi Pat

    Ok I admit it, I should have refreshed my memory on the old post before starting out. I'll have a go at testing to see if there is much difference in performance between the two methods. At least it has helped me develop a working days formula for a Crystal Report, which now includes Easter.

    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
  •