How can I count workdays in Access?

[Guest]

I have a database in MS Access which tracks a number of transactions in our
office. One of the items I must report on (with Reports and Queries) is the
number of workdays between initiating a certain action and producing the
results. I do not want to include holidays or weekends. How can I do that?
Any advice is greatly appreciated!

 

[Dirk Goldgar]

I have a database in MS Access which tracks a number of transactions
in our office. One of the items I must report on (with Reports and
Queries) is the number of workdays between initiating a certain
action and producing the results. I do not want to include holidays
or weekends. How can I do that? Any advice is greatly appreciated!

Check out these two links:

http://www.mvps.org/access/datetime/date0006.htm

http://www.mvps.org/access/datetime/date0012.htm

 

[(PeteCresswell)]

I do not want to include holidays or weekends.

Weekends, no prob - but for the holidays you'll need a table.

Here's something that's kind of similar:
-------------------------------------
Function businessDayPreviousGet() As Date
2000 debugStackPush mModuleName & ": businessDayPreviousGet"
2001 On Error GoTo businessDayPreviousGet_err
' PURPOSE: To return the business day prior to today's date
' RETURNS: Date of business day prior to today

2002 Dim myRS As Recordset

Dim myPBDay As Date
Dim myHoliday As Boolean

' Set previous business day holder to yesterday
2100 myPBDay = date
2110 myPBDay = DateAdd("d", -1, myPBDay)
2120 myHoliday = True

2140 Set myRS = curDB().OpenRecordset("tlkpHoliday", dbOpenDynaset)

' Check for a date on the weekend or on a holiday
2150 Do Until myHoliday = False
2160 If WeekDay(myPBDay) = 1 Or WeekDay(myPBDay) = 7 Then
2170 myPBDay = DateAdd("d", -1, myPBDay)
2180 Else
2190 myRS.FindFirst "HolidayDate = #" & myPBDay & "#"
2200 If myRS.NoMatch Then
2210 myHoliday = False
2220 Else
2230 myPBDay = DateAdd("d", -1, myPBDay)
2240 myHoliday = True
2250 End If
2260 End If
2270 Loop

2999 businessDayPreviousGet = myPBDay

businessDayPreviousGet_xit:
debugStackPop
On Error Resume Next
myRS.Close
Set myRS = Nothing
Exit Function

businessDayPreviousGet_err:
bugAlert True, ""
Resume businessDayPreviousGet_xit
End Function