Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding weekdays (Access 2002 SP3)

    I want to create a date on a report that adds ten weekdays to the current date and returns the next weekday(If the return date is a Saturday or Sunday-return the following Monday)

    The expression:
    =IIF(Weekday(DateAdd("w",11,Date()))=7,DateAdd("w" ,13,Date()),DateAdd("w",DateAdd("w",11,Date)))
    will work fine and account for a Saturday returned date and move it to the next Monday.

    The expression
    =IIF(Weekday(DateAdd("w",11,Date()))=7,DateAdd("w" ,13,Date()),IIF(Weekday(DateAdd("w",22,Date()) =1,DateAdd("w",12,Date())=1,DateAdd("w"w,12,Date() )DateAdd("w",DateAdd("w",11,Date)))
    Does not return anything. Does Access support nested IIF's in Control Source expressions?
    This seems like a complicated expression, is there an easier way?

    Thanks for any help,
    Carla

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

    Re: Adding weekdays (Access 2002 SP3)

    I suspect something has gone wrong when you pasted the second expression. Here is one that should do what you want:

    =DateAdd("d",11,Date())+IIf(Weekday(Date(),3)<3,3-Weekday(Date(),3),0)

    This expression uses "d" instead of "w", and Weekday(...,3). The 3 means that days start counting at 1 on Tuesday instead of Sunday. If the weekday is 1 or 2, i.e. on Tuesday or Wednesday, the date 11 days in the future is a weekend day. In that situation, we add (3 - weekday) extra days to get to the next Monday.

  3. #3
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding weekdays (Access 2002 SP3)

    Thanks Hans,

    You are correct I did paste the incorrect second expression
    Expression you sent worked perfect, I only had to adjust the numbers 1 day forward to make 10 full days after the current date.

    Carla

Posting Permissions

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