In Excel you can calculate the number of days between 2 dates and elminate the weekends as well as any holidays using the NETWORKDAYS() function. Is there a way to do a similar thing in Access? Please give details if its possible... (in Access 97)

Thanks,

Brian

[This message has been edited by BC_Randall (edited 10-15-2001).]

This code will calculate the number of workdays between two dates. The answer counts the first date entered as a work day unless it is a weekend or holiday.

You will need to create a two field table called 'tblHolidays' with a field for the name of the holiday and a date field called 'HoliDate'.

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("HoliDate", "tblHolidays", "[HoliDate]=#" & DateCnt & "#")) Then
EndDays = EndDays - 1
End If

If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = EndDays

End Function