DateAdd behavior

  • Thread starter Thread starter rob
  • Start date Start date
R

rob

Hey folks,

I have a date field (Dept_Suspense) that I use to update 2 other fields:

Report_Suspense = Dept_Suspense + 30 Days
Office_Suspense = Dept_Suspense - 10 Days
* QC_Suspense = Dept_Suspense + 5 workdays

Easy stuff right? The problem is with the QC_Suspense...

I tried the following (To simplify things I'll use todays date of 7 Feb,
which is a Tuesday):
DateAdd("w", 5, Date)

I expected this to give me a date of 14 Feb 06, but it gave me a date of 12
Feb 06
(I'm assuming "w" is weekdays, as in: Mon, Tues, Wed, Thurs, Fri)

I experimented with DateAdd("d", 5, Date) and got the same results.

Do I have the wrong expectation with this Function? If not, are their known
bugs?

Any help would be greatly appreciated
 
I've never been able to figure out "w" as weekday in this function. It
really shouldn't be there at all as far as I am concerned. In the DatePart
function, it returns the number of the day of the week (values 1 to 7).

In your particular case, you can just add 7 days to the Dept_Suspense (of
course, this ignores holidays).
DateAdd("d", 7, Date)
or add a week
DateAdd("ww", 1, Date)

If you need something more complex/accurate then you an check out the
following URL for a sample of how to do this including holidays.
http://www.mvps.org/access/datetime/date0006.htm
 
I gave up! As a workaround, I used the weekday function to determine the
day of the week (1 - 7), then added the appropriate amount of days to ensure
I suspensed the proper number of weekdays using Select Case statements.

Sun (+ 5)
Monday - Friday (+ 7)
Sat (+ 6)

It works, but I know there must be an easier way!
 
Back
Top