how to calculate date()+20weekdays?

C

Cynthia

I need to update values in a certain field (data type is date) by adding 20
weekdays to them. How could I do it?

If I simply use +20, it'll include weekend as well....the values maybe any
day of the week, and I may also need to add some other number of weekdays to
the data (instead of +20).

Thanks for the help!
 
B

Baz

If you look up the DateAdd function in Help, you will see that one of the
Interval options is "w", which is weekdays.
 
C

Cynthia

I tried, but DateAdd("w",7,[fieldname]) would return the same result as
[fieldname]+7.....why does this happen? How could I fix it? thanks a lot Baz
 
J

Jamie Collins

I need to update values in a certain field (data type isdate) by adding 20
weekdays to them. How could I do it?
Click to expand...

SELECT M1.my_datetime_col,
(
SELECT C1.dt
FROM Calendar AS C1
WHERE C1.isWeekday = 1
AND 20 =
(
SELECT COUNT(*)
FROM Calendar C2
WHERE C2.dt >= M1.my_datetime_col
AND C2.dt <= C1.dt
AND C2.isWeekday = 1
)
) AS my_new_datetime_col
FROM MyTable AS M1;

Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519

Jamie.

--
 
D

Dale Fye

Cynthia,

Another techinque is to create a short function, something like:

Public Function WeekdayAdd(StartDate As Date, _
DaysToAdd As Integer) As Date

'Accepts both positive and negative values of DaysToAdd

Dim intDays As Integer
Dim myDate As Date

intDays = 0
myDate = StartDate
Do While intDays <> DaysToAdd

myDate = myDate + Sgn(DaysToAdd)
If Weekday(myDate) > 1 And Weekday(myDate) < 7 Then
intDays = intDays + Sgn(DaysToAdd)
End If

Loop
WeekdayAdd = myDate

End Function

HTH
Dale
 
J

John W. Vinson

If you look up the DateAdd function in Help, you will see that one of the
Interval options is "w", which is weekdays.
Click to expand...

Unfortunately, Baz, the "w" interval is pretty much useless. "w" and "d" are
functionally synonymous:

?date
8/6/2007
?dateadd("d", 7, date)
8/13/2007
?dateadd("w", 7, date)
8/13/2007

You need to use VBA code and an auxiliary table of holidays to get workdays.
See

http://www.mvps.org/access/datetime/date0006.htm

for some sample code.

John W. Vinson [MVP]
 
C

Cynthia

This is very helpful. Thanks a lot.

But how come my Access shows "function undefined" message when I use it?
(my SQL is "select WeekdayAdd(FieldName,20) as NewDate from TableName")

Where is my mistake?
 
B

BruceM

Select everything from Public Function ... through End Function, and paste
it into the form's code module. Until you do this, Access will not
recognize the function when you try to use it.
 
D

Douglas J. Steele

Probably better to store it in a standalone module, so that it'll be
accessible from other forms as well.
 
C

Cynthia

thanks a lot! You guys are great!

Douglas J. Steele said:
Probably better to store it in a standalone module, so that it'll be
accessible from other forms as well.
Click to expand...
 
Top