Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  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


    On reviewing the solutions and other websites (such as http://www.mvps.org/access/index.html) there always seems a problem with catering for holidays. Specifically, for all the solutions catering for holidays you have to enter the specific holiday dates, and I got to thinking that there must a better way of doing this.

    I have gotten 95% of the way, but as usual I have over engineered the code and there must be a simpler way. So I have attached the test DB for hopefully some friendly peer review and pointers on improving it. While there is probably a better solution from those more experienced than myself, it has been a good learning experience for me.

    A few issues for comment:

    1 It does not give a correct solution in some instances eg 17 Apr 03 plus 4 working days gives the wrong date.
    2 It runs awfully slow for a big working day number.
    3 I had troubles getting the dates to read correctly for checking, hence the temporary holiday table. I tried all sorts of solutions such as format, Cdate, dateserial and #, but could not get a consistent result.

    Also a few pointers for assistance:

    1 There are 3 tables for a) storing fixed holidays, [img]/forums/images/smilies/cool.gif[/img] storing Easter holidays (no real solution for this issue), and c) temp table for storing actual fixed holidays to be used in calculating working days
    2 The relevant two forms
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Working Days (Access 97)

    You wrote:
    >>Specifically, for all the solutions catering for holidays you have to enter the specific holiday dates, and I got to thinking that there must a better way of doing this.<<

    I not so sure. The advantage of having a holiday table in which you enter specific dates is that it is easily customizable for every situation. If you have code to automatically figure out things (like when is Thanksgiving, etc.), it becomes a problem when the holidays you expect to have aren't honored in a given situation. For example. in most states Good Friday is a holiday, but in North Carolina that was a workday and the Monday after Easter was a holiday! (at least it was when I lived there some years ago)

    I can see having a routine to automatically populate the holiday table, this would allow users to delete/add holidays as needed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  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)

    What I would do is to create a dates table (let us call it tblDates) that would hold a range a range of dates that people are allowed to enter (this could be 1/1/2000 till 31/12/2009) and set the appropriate fields for Holidays and Special (for the 3rd Monday in a certain month). The table would have the following fields:

    DateInTable this obviously holds the date
    DayOfWeek this holds the number for day of week (eg. 1 for Sunday, 2 for Monday)
    Holiday this is a yes/no field set to true if it

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 97)

    Don't know if you have checked out my recent threads on the subject, but i'm probably doing a similar thing to you at the moment.

    The code below is from a faq on the tek-tips forum.. ( i went there accidentally, honest charlotte! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> )

    This only uses 1 table to record public holidays. If you are using this for non-attendance reports, i don't see the requirement to record individual holiday days, because holidays are by default, permissable.

    <font color=blue>Public Function Deltadays(StartDate As Date, EndDate As Date, StDate As Variant, LcDate As Variant, disc As String)
    Dim rstHolidays As Recordset
    Dim Idx As Long
    Dim MyDate As Date
    Dim Numdays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1
    Dim dbs As Database
    Dim Beginning As Date, Ending As Date
    Dim JoinDate As Date, LeaveDate As Date
    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)
    NumSgn = Chr(35)
    MyDate = Format(StartDate, "Short Date")
    For Idx = Beginning To Ending

    Select Case (Weekday(MyDate))
    Case Is = 1 ' Sunday
    ' do nothing
    Case Is = 7 ' Saturday
    ' do nothing
    Case Else ' Normal Workday
    strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
    rstHolidays.FindFirst strCriteria
    If (rstHolidays.NoMatch) Then
    Numdays = Numdays + 1
    Else
    ' do nothing
    End If
    End Select

    MyDate = DateAdd("d", 1, MyDate)
    Next Idx

    Deltadays = Numdays

    End Function</font color=blue>

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 97)

    I created a program that calculates Federal Holidays for a Leave Record in Excel. the method for calculating holidays should be easily adaptable to Access; I did everything in "table" format since I don't know Excel very well. It seems to work; no one's complained in over 2 years, and I even use it myself.

    Hope it helps,
    Attached Files Attached Files

  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 (Access 97)

    With all due respect, the code you posted is not doing a similar thing at all. For a start WTH needs to add nn working days to a starting date, not calculate the working days between two dates.

    What I was trying to show was that he could keep a table which shows holidays, shows the weekday and shows any specials that he cares to set.
    With all that in place I just provided a query to select the Top nn (number of working days) records and then select the top 1 of those.

    I will post the amended database if people require it.

    Pat

  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

    Thanks for all the input.

    Some further comments. I realise holidays will vary from region to region, thus the DB can only ever be set up for a region. In my case, because I work for a Government Depart we are subject to a law that specifies what are public holidays so this is not an issue for me. I will at some stage need to develop it for number of days between two working dates, but the first priority is to work out the end date based on working days.

    I would be interested in seeing Pat's amended DB.

    Thanks again.

    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 (Access 97)

    I have included your amended database and you will find that there are extra forms, like:
    frm Dates which I use as a verification that my date calcs are working
    frm Generate tblDates this generates an entry in tblDates for each date between a keyed in start and end date
    frm Update tblDates from tbamonthdays this updates the tblDates table with those tbamonthdays entries where DayofWK is null. I haven't worked out yet how to update the table for the other type of entries yet.

    I have changed the form frmWorkingdays to show the calculated end date of the start date and working days that are entered. I hould have put an extra check in there to check if it can calculate the nn working days, in other words, have enough dates been generated in the table tblDates.

    A couple of the queries are not used, eg. the qry Years and qry tblDates.

    If you have any problem understanding this, just post.
    HTH
    Pat
    Attached Files Attached Files

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

    I have made a few changes since I last posted, they are:
    frm Update tblDates from tbamonthdays this updates the tblDates table with those tbamonthdays entries where DayofWK is null. I have now worked out how to update the table for the other type of entries (eg. 3rd Monday of January).

    I have changed the form frmWorkingdays to show the calculated end date of the start date and working days that are entered. I have put an extra check in there to see if it can calculate the nn working days, in other words, have enough dates been generated in the table tblDates.

    A couple of the queries that were not used now are used, eg. the qry Years and qry tblDates.

    I have included a new query that calculates the number of working days between 2 dates, it is called [qry Calc Working Days between 2 Dates]. This query uses the same table structure as the other does.

    Pat
    Attached Files Attached Files

  10. #10
    New Lounger
    Join Date
    Jul 2001
    Location
    Iowa, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 97)

    Refer to post 127952. The email address in the attachment to that post has changed, but the code's still valid and could be modified for NZ instead of USA. Also, here's an algorithm I found for Easter that could be made a part of the code in the referenced post.

    Algorithm from anonymous correspondent from New York to Nature in 1876

    In the text below, / represents an integer division neglecting the remainder, while % is division keeping only the remainder (MOD). So 30/7=4 ,

    and 30%7=2 .

    a=year MOD 19
    b=year/100
    c=year MOD 100
    d=b/4
    e=b%4
    f=(b+8)/25
    g=(b-f+1)/3
    h=(19*a+b-d-g+15)MOD 30
    i=c/4
    k=c%4
    l=(32+2*e+2*i-h-k)%7
    m=(a+11*h+22*l)/451
    Easter Month =(h+l-7*m+114)/31 [3=March, 4=April]
    p=(h+l-7*m+114)MOD 31
    Easter Date=p+1 (date in Easter Month)

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

    What happens if I live in other than the US, these holidays are not applicable.
    Pat

  12. #12
    New Lounger
    Join Date
    Jul 2001
    Location
    Iowa, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 97)

    In the code from the other posts, just change the Select Case Month(datCurrDate) to cover your country/region. For example, February would be changed like this for New Zealand:
    Case 2
    If Day(datCurrDate) = 6 Then ' For Waitangi Day
    datChkDate = datCurrDate
    End If
    If datChkDate = datCurrDate Then booHol = True

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

    So if you had a package that was used all over the world you would need different source code for each Country. This is one of the reasons that I put these details into a table.
    Pat

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

    Nifty algorithm.

    Is the Easter Date mentioned the Sunday?
    Pat

  15. #15
    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 all the advice (particularly from Pat for his time and effort on the DB). There is certainly plenty of food for thought for me on how to manage work days and holidays.

    Cheers

    WTH

Page 1 of 2 12 LastLast

Posting Permissions

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