DateAdd behavior

[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

 

[John Spencer]

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

 

[rob]

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!