If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date question
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. -- Thanks for any suggestions |
#2
|
|||
|
|||
On Wed, 16 Mar 2005 16:03:10 -0800, "Spectra"
wrote: 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] |
#3
|
|||
|
|||
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" wrote: On Wed, 16 Mar 2005 16:03:10 -0800, "Spectra" wrote: 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] |
#4
|
|||
|
|||
On Thu, 17 Mar 2005 15:25:01 -0800, "Spectra"
wrote: 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] |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 Spectra wrote: 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Date Add Question | PMK | General Discussion | 4 | February 5th, 2005 03:02 PM |
SUMPRODUCT with date range question | Rob V | General Discussion | 1 | January 31st, 2005 03:55 PM |
Template / date question | Frank Kabel | Worksheet Functions | 0 | November 17th, 2004 06:24 AM |
Filtering on custom date field | MED | Contacts | 3 | September 17th, 2004 03:05 PM |
Date query in Dialog box question | joe | Running & Setting Up Queries | 1 | June 5th, 2004 12:52 AM |