DATEADD should skip the weekends

[Eric]

Is there any easy within an append query to use the dateadd function that
will skip the weekends. I have this in my query;

nolaterthan: dateadd("d", 10, Date())

In my application nolaterthan is used for my memo's to sections where it
mentions that the memo has to be signed and returned no later than the date
stored in 'nolaterthan'. But the thing is that the dateadd should skip the
weekends so that it does not show any dates on saturdays or sundays. Is there
way to accomplish that?

Many thanks.

 

[Douglas J. Steele]

Take a look in the Date/Time section of "The Access Web"
http://www.mvps.org/access

There are a couple of options there.

 

[Eric]

Many thanks I appreciate this very much, I have found the solution by using
these functions:

************* Code Start *************
Public Function HowManyWD(FromDate As Date, _
ToDate As Date, _
WD As Long)
' No error handling actually supplied
HowManyWD = DateDiff("ww", FromDate, ToDate, WD) _
- Int(WD = WeekDay(FromDate))
End Function
'************* Code End *************

In the same way, someone can easily get the number of weekdays
(excluding weekends) by subtracting number of Sundays and Saturdays:

'************* Code Start *************
Public Function HowManyWeekDay(FromDate As Date, _
ToDate As Date, _
Optional ToDateIsIncluded As Boolean = True)

HowManyWeekDay = DateDiff("d", FromDate, ToDate) - _
ToDateIsIncluded - _
HowManyWD(FromDate, ToDate, vbSunday) - _
HowManyWD(FromDate, ToDate, vbSaturday)
End Function
'************* Code End *************

 

[WireGuy]

Do you need to set up a table with a date & day of week fields? How do you
run this procedure? On Open of a form or???

Thanks,
John