Terry:
Two ways you can do this:
1. Create a function in a standard module, such as:
Public Function WorkDaysDiff(varLastDate, varFirstDate)
Dim lngDaysDiff As Long, lngWeekendDays As Long
Dim intPubHols As Integer
If IsNull(varLastDate) Or IsNull(varFirstDate) Then
Exit Function
End If
' if first date is Sat or Sun start on following Monday
Select Case Weekday(varFirstDate, vbMonday)
Case vbSaturday
varFirstDate = varFirstDate + 2
Case vbSunday
varFirstDate = varFirstDate + 1
End Select
' if last date is Sat or Sun finish on following Monday
Select Case Weekday(varLastDate, vbMonday)
Case vbSaturday
varLastDate = varLastDate + 2
Case vbSunday
varLastDate = varLastDate + 1
End Select
' get total date difference in days
lngDaysDiff = DateDiff("d", varFirstDate, varLastDate)
' get date difference in weeks and multiply by 2
' to get number of weekend days
lngWeekendDays = DateDiff("ww", varFirstDate, varLastDate, vbMonday) * 2
' subtract number of weekend days from total date difference
' to return number of working days
WorkDaysDiff = lngDaysDiff - lngWeekendDays
End Function
and call it in the query:
WorkDaysDiff([datereceived], [datesent])
2. Create a Calendar table, which is simply a table of all dates over a
given period. An easy way is to fill a column in Excel and import it into
Access. You can then simply count the days in the table between two dates
excluding the weekends, i.e.
WHERE Weekday(calDate, 2) < 6.
Ken Sheridan
Stafford, England
