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.a u...

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.a u...

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(ByVal 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.