You will not be able to do that using SQL. Your need a function to calculate
that for you and return it into a calculated field. Here is a function that
will do it. Note that it includes a DLookup to a table named Holidays and a
field named Holdate. This is used to eliminate holidays (non working days)
from the count. If you have your own table for this purpose, you could
change the code. If you want to create a table of your own, you only need
two fields. Holdate is a Date/Time field that contains the date of non
working days (You don't have to worry about weekends, they take care of
themselves). The other field is just a Description so you will know what the
date represents. It is not required, because this routine doesn't use it.
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned
Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select
intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function
Now to use it:
DaysOut: AddWorkDays([TheDate], 30)
