Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0)

    How To Add 10 Work Days to Posting Date?
    a2k (9.0.3821) SR-1 Jet 4.0

    I have the following code on a PostingDate After_Update Control.

    NoticeDueDate = DateAdd("d", 10, PostingDate)

    03/05/03 returns 03/15/03

    How do I add 10 Working Days to PostingDate so it works as follows:

    03/05/03 returns 03/19/03


    Thanks, John

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    Do a search on Work Days - there are numerous recent threads on the subject.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    Here you go, I've added another field to specify how many days to remind in.

    Have fun.
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    Hi Dave

    Thanks for the example

    When I enter 3/5/3 it returns 3/18/3, should'nt it be 3/19/3?

    John

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    John, check the following post number, it may help you:- <post#=189930>post 189930</post#>

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    <P ID="edit" class=small>(Edited by WendellB on 05-Mar-03 13:23. activate link)</P>Hi Wendell

    I tried NoticeDueDate = DateAdd("w", 10, PostingDate) without success, what is w=weekday suppose to do? In my case it added 10 to PostingDate

    I found the following solution in one of the posts you suggested I search

    The Access Web
    http://www.mvps.org/access/datetime/date0012.htm

    Thanks, John

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    <P ID="edit" class=small>(Edited by WendellB on 05-Mar-03 15:30. Correct original SWAG and add link to KBase article)</P>Confusion is rife over what the "w" choice for interval is supposed to do. My understanding is that it adds 10 days, then checks to see if that is a weekday (as opposed to a weekendday) and adjusts it in the appropriate direction until it hits a weekday. But I haven't found anything in help (or any where else) that confirms that. Maybe somebody else has more empirical evidence - I only used it once, and I don't remember why.

    Note that there is an issue with holidays as well that gets fairly complex. I haven't checked out the page you referenced, but there is a detailed description of how to do that sort of thing in the Microsoft Knowledge Base. Do as search under Access 2000 and search for weekday.

    [/i]<font color=blue>Well I was wrong on all counts - in the DateAdd and DateDiff functions it works exactly the same as "d" but for some reason it didn't show up in the KBase search. But check out this article Weekday doesn't work as expected</font color=blue>[/i].
    Wendell

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    John
    The example I sent you gives the tenth day after the Posting Date, If you want the "Next" date, in the "WorkDayAdd" function, change the line that states:

    WorkdayAdd = d
    to
    WorkdayAdd = d + 1

    This will return the day after the tenth.
    I hope this is what you wanted.
    Good luck.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    I want to add only working days including holidays. I looked at the microsoft KB artcle 210064 -HOW TO: Determine If a Date Falls on a Weekend or on a Holiday in Access 2000.

    I can get a function that returns a value of true if "theDate" is a non working day i.e. a weekend or a holiday.

    How can I use this with the DateaddW function to include holidays?

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    Hi Wendell

    Great detective work.

    Your find is better than the following because it will also handle adding a negative number of days.

    The Access Web http://www.mvps.org/access/datetime/date0012.htm

    Thanks, John

  11. #11
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    Ummm....I read through this thread...and I'm just curious, if you want to get a date 10 'working' days from now...just add 14. That is if you don't 'skip' holidays. If you think about it, if you want to go to the next Monday, you add 7 days. That is a week. However, there are only 5 working days in a week. Thus, if you add 5 working days to Monday, you'll get the next Monday. (Same as adding 7). Therefore, to get the date from 10 working days, it is always going to be +14. (Unless you are making your original date a Saturday or Sunday...then this theory goes away).

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

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    What you write is very true for this specific example. But what if the "10" is variable? It might be the value of a text box on a form. In that case, you need a function.

  13. #13
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    I know. Just applying the KISS method. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    Our system is a little more complicated than just choosing to remind in let's say 10, or 7 days time.

    We create an estimate which comprises of hours to repair a vehicle.
    To get our working date for the vehicle to be complete, we have to use a very simple formula.
    Double the hrs, multiply by 2 then divide by 8.
    So a 4 hr job actually returns 1 day to repair. It's quite accurate really.
    As you can assume from this, there are multi - multi variables to contend with.
    Given that we now have the variable amount, we acn use this to calculate a future working date.
    The example I posted above does just that, so given "any" variable, we can be confident to return a working date.

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

    Re: How To Add 10 Work Days to Posting Date? (a2k (9.0.3821) SR-1 Jet 4.0

    <P ID="edit" class=small>(Edited by patt on 07-Mar-03 09:12. Refer to extra day and holiday)</P>What if that extra day is a weekend or a holiday?
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

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
  •