Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Public Holidays (MS ACCESS 2003)

    I have an appointment database that allows me to make appointments for people 2 full working days in advance. IE ( if entering the appt on MONDAY then the earliest that i can make the appt is THURSDAY )

    I have a query that calculates this all for me and gives me a list. Now heres the question.

    I have managed to get to ignore public holidays when making the appt. Meaning that i can't make an appt on a public holiday but i am wondering if it is possible to adjust the query or the formular in the query to get it to ignore them when counting the working days. I have added another table with them all listed in it.

    Here is a sample of the formular that i am using to get the next available appt date.

    >Date()+IIf(Weekday(Date())=7,3,IIf(Weekday(Date() +2)<3 Or Weekday(Date()+2)=7,4,2))
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

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

    Re: Public Holidays (MS ACCESS 2003)

    Welcome to Woody's Lounge!

    See Date/Time: Doing WorkDay Math in VBA on the Access Web for a series of functions for working with working days, weekend days and holidays.

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Holidays (MS ACCESS 2003)

    Hans,

    Thankyou for the help. I am only new to VBA but know enough to read through the page and then pull out and edit the bits i feel appropriate to my needs.

    If you anyone has a non VBA suggestion then i would be glad to listen to that as well
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Public Holidays (MS ACCESS 2003)

    Hi there

    Creating a formula is probably going to be more difficult that the VB approach. The code Hans has provided you is a custom function specifically for this.
    Jerry

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

    Re: Public Holidays (MS ACCESS 2003)

    In this particular case, it might be possible to create an expression that calculates the date taking holidays into account, but I fear it would be a very complicated expression. Using VBA hides this complexity, making the query easier to read.

  6. #6
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Holidays (MS ACCESS 2003)

    being busy with work i haven't had much chance to play with it yet but I think i should be able to struggle through it.

    and i know the place to come if i have any problems.

    Thanks for all the prompt posts
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  7. #7
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Holidays (MS ACCESS 2003)

    I just thought that you all might like to know that I managed to get it to work.

    I have also had a trawl through the site you gave Hans and it has a lot of helpful stuff in it.

    Thanks everyone again for your assistance

    Regards
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

Posting Permissions

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