Date question

[Guest]

I have 2 dates, date delivered & date due. The date due is the date delivered
+ 3 days. If the due date ends up on a Saturday, I want to add 2 days to the
date due date. If the due date ends up on a Sunday, I want to add 1 day to
the due date.

 

[John Vinson]

I have 2 dates, date delivered & date due. The date due is the date delivered
+ 3 days. If the due date ends up on a Saturday, I want to add 2 days to the
date due date. If the due date ends up on a Sunday, I want to add 1 day to
the due date.

Date Due could be calculated with the expression:

DateAdd("d", 3 + Switch(Weekday(Date()) = 5, 1, Weekday(Date()) = 6,
2, True, 0), Date())


John W. Vinson[MVP]

 

[Guest]

I'm sorry, I should have spelled my question out a little better. The
function added 3 days to the current date, but I need the 3 business days
added to the delivered date that equals the due date.

1. How do I add the three business days to the "delivered" date in your
function provided?

Monday delivered date = Thursday due date
Tuesday delivered date = Friday due date
Wednesday delivered date = Saturday due date, but switch to Monday due date
Thursday delivered date = Sunday due date, but switch to Tuesday due date
Friday delivered date = Monday due date, but switch to Wednesday due date

Also, does Access recognize the days of the week like this?
Sunday=1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, Saturday=7

One other question, if I wanted to insert specific holidays, how would I say
this?

Thank You So Much for Your Help!!!

 

[John Vinson]

I'm sorry, I should have spelled my question out a little better. The
function added 3 days to the current date, but I need the 3 business days
added to the delivered date that equals the due date.

Just change Date() to the name of the delivered date field.

One other question, if I wanted to insert specific holidays, how would I say
this?

Have a table of holidays, with one record for each day in the holiday
(e.g. if Thanksgiving Day and the Friday after it are holidays,
there'd be two records in the table). Add

DCount("*", "[Holidays]", "[HolidayDate] >= [DeliveredDate] AND
[HolidayDate] <= " <the same date-add expression> ")

John W. Vinson[MVP]

 

[Guest]

The function added the 3 days to the delivered date but it didn't switch any
of the days. I typed the function like this:
Due Date:
DateAdd("d",3+Switch(Weekday([dtmdelivered])=7,2,Weekday([dtmdelivered])=1,3,Weekday([dtmdelivered])=2,4,True,0),[dtmdelivered])

Do you see anything wrong?

Delivered: Due: Switch to:
Mon Thu
Tue Fri
Wed Sat Mon
Thu Sun Tue
Fri Mon Wed

Does Access look at the weekdays as numbers?
Sun=1
Mon=2
Tue=3
Wed=4
Thu=5
Fri=6
Sat=7

Can you see what might have gone wrong to prevent the switch?

 

[Andreas]

Have a look at the help for the function.
There you will see that Access looks at the weekdays as numbers as per
the table you provided at the bottom of this message (or have you had a
peek already ).

For the data given, I believe your function should look like the
following. I added some line breaks for readability.

DateAdd("d", 3 +
Switch(
Weekday([dtmdelivered])=4,2,
Weekday([dtmdelivered])=5,2,
Weekday([dtmdelivered])=6,2,
True,0)
,[dtmdelivered])

Regards,
Andreas