Adding weekdays (Access 2002 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote