Date Calculations

[G]

If one was to calculate say ten days ahead from a certain date but did not
want to include the weekdays in this count, what formula would i use in my
query column heading?
thanks.

[Ruskin Hardie]

Don't think you can do this directly in a query... However, what you could
do, is loop through the records in the query and calculate the date, then
update a temporary table and base your report/form on the temporary table,
that now holds the newly calculated date....

Dim curDate As Date
Dim addCount As Long
Dim tmpDate As Date

curDate = Now
tmpDate = curDate
addCount = 0
Do Until addCount = 10
tmpDate = tmpDate + 1
If Format(tmpDate, "w") = 1 Or _
Format(tmpDate, "w") = 7 Then
'Saturday and Sunday
curDate = tmpDate
addCount = addCount + 1
Else
'Weekdays - not counting these,
'so do nothing
End If
Loop


"G" <sk**@optusnet. com.au> wrote in message
news:3f******** *************** @news.optusnet. com.au...

If one was to calculate say ten days ahead from a certain date but did not
want to include the weekdays in this count, what formula would i use in my
query column heading?
thanks.

[Ian Hinson]

"G" <sk**@optusnet. com.au> wrote in message
news:3f******** *************** @news.optusnet. com.au...

If one was to calculate say ten days ahead from a certain date but did not
want to include the weekdays in this count, what formula would i use in my
query column heading?
thanks.

This function returns the date of the week day (ie not Sat or Sun) which is
"iDaysToAdd " week days after dtStart

Function AddWeekDays(ByV al dtStart As Date, ByVal iDaysToAdd As Integer) As
Date
Dim dtTemp As Date

dtTemp = DateAdd("ww", iDaysToAdd \ 5, dtStart) ' add whole weeks first
dtTemp = DateAdd("d", iDaysToAdd Mod 5, dtTemp) 'add on part-week days
If Weekday(dtTemp, vbMonday) > 5 Then dtTemp = DateAdd("d", 2, dtTemp)
AddWeekDays = dtTemp
End Function

Tested code.