Copy the function.
Open a VBA module
Paste the function into the module. Save the module with a name other than
the name of the function.
Then call the function by giving it two dates
WorkingDays(#1/1/2008#,#1/31/2008#)
That will return then number of working days in the month
Personally, I have become a fan of including a calendar table in my
databases that allows me to answer this question and many others with a
query using the calendar table. The simplest version of the table would
have three fields :
TheDate (Date field with one entry for all dates in a period of time)
IsWeekDay (Yes/No field - True = M to Friday)
IsHoliday (Yes/No field - True = this is a holiday)
A 150 years of data takes up less than 3 megabytes of storage. And the
table can be constructed fairly easily with a VBA routine and an update
query.
With the table, your problem is solved with
SELECT Count(TheDate), Format(Thedate,"mmm")
FROM CalendarTable
WHERE Year(TheDate) = 2008
AND IsWeekDay = True
AND isHoliday = False
The following routine makes it easy to create the table. You will need to
figure out how to populate the holidays
Public Sub sBuildCalendarTable(dStartDate As Date, _
dEndDate As Date, _
Optional tfOptionalHolidays As Boolean =
True)
Dim strSQL As String
Dim dbAny As DAO.Database
Set dbAny = DBEngine(0)(0)
'================================================
' Build the Calendar Table
'================================================
strSQL = "CREATE TABLE CalendarTable (" & _
" TheDate DateTime Constraint PKTheDate Primary Key" & _
", IsWeekDay YesNo " & _
", IsHoliday YesNo " & _
", HolidayName Text(50)" & _
")"
dbAny.Execute strSQL, dbFailOnError
sFillCalendarTable dStartDate, dEndDate
'================================================
' Populate Weekday field
'================================================
strSQL = "UPDATE CalendarTable " & _
"SET CalendarTable.IsWeekday = " & _
"Weekday([theDate]) In (2,3,4,5,6)"
dbAny.Execute strSQL, dbFailOnError
End Sub
Private Sub sFillCalendarTable(dStart, dEnd)
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim iCount As Long
On Error GoTo sFillCalendarTable_Error
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT TheDate " & _
" FROM CalendarTable" & _
" WHERE TheDate is Null")
With rst
For iCount = 0 To DateDiff("d", dStart, dEnd)
.AddNew
rst!TheDate = DateAdd("d", iCount, dStart)
.Update
Next iCount
End With
Exit Sub
sFillCalendarTable_Error:
Resume Next 'Assumption that error is caused
'by trying to add duplicate record
End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
