To recap, our goal is to calculate the total number of work days between two dates. In the last several issues we've:

• Laid out the logic for the process - May 07
• Made the case for a holiday table - May 07
• Developed a function to calculate the total number of weekdays between two weeks- June 07
• And last month we developed a SQL statement to count the number of workdays between two dates and developed a function that will use that SQL statement to create a recordset to calculate the total number of work days that are days off due to holidays - August 07

This month we bring things home with two final steps. First we'll review the tricky parts of the function we wrote last month, and then we'll write our final function that will get us to the goal that we've sought now for five months.

We developed the following function last month:

Function HolidaysOffCount(dtmBegin As Date, dtmEnd As Date) As Long
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim rv As Long
   Set db = CurrentDb
   strSQL = "SELECT tblHoliday.HolidayDate, tblHoliday.Workday, " & _
   "Format([HolidayDate],'ddd') " & _
   "FROM tblHoliday " & _
   "WHERE (((tblHoliday.HolidayDate) >=#1/1/2007# " & _
   "And (tblHoliday.HolidayDate) <=#12/31/2007#) " & _
   "AND ((tblHoliday.Workday)=False) " & _
   "AND ((Format([HolidayDate],'ddd'))<>'Sat' " & _
   "And (Format([HolidayDate],'ddd')) <>'Sun'));"
      Set rst = db.OpenRecordset (strSQL)
   If rst.EOF And rst.BOF Then
     rv = 0
     Else
     rst.MoveLast
     rv = rst.RecordCount
   End If
   HolidaysOffCount = rv
End Function

Our function has one major problem - it's inflexible. I've bolded the lines that cause the problem. The dates are hard coded. No matter what dates are passed in, the function as it stands uses 1/1/2007 as a begin date and 12/31/2007 as an end date. To make our function flexible we'll change the lines above to:

"WHERE (((tblHoliday.HolidayDate)>=#" & dtmBegin & "# " & _ "And (tblHoliday.HolidayDate)<=#" & dtmEnd & "# " & _

With this change we replace the hard coded dates with the dates passed in by our function. The only other part that may be less than immediately clear is the section that says:

   Set rst = db.OpenRecordset(strSQL)
   If rst.EOF And rst.BOF Then
      rv = 0
      Else
      rst.MoveLast
      rv = rst.RecordCount
   End If
   HolidaysOffCount = rv

This section essentially says to use the SQL statement (with the passed in dates) to create a record set (a set of records that we can inspect or act on). If the record set, when first opened, is at the beginning and at the end we have no records, therefore return a zero, otherwise move to the last record and then whatever the record count is, return that as the number of records. Why move to the last record? When Access opens a record set it doesn't know how many records there are until it gets to the end, so it moves there and then it knows how many records it is dealing with.

So our completed Holiday function is:

Function HolidaysOffCount(dtmBegin As Date, dtmEnd As Date) As Long
   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim rv As Long
   Set db = CurrentDb
   strSQL = "SELECT tblHoliday.HolidayDate, tblHoliday.Workday, " & _
   "Format([HolidayDate],'ddd') " & _
   "FROM tblHoliday " & _
   "WHERE (((tblHoliday.HolidayDate) >=>=#" & dtmBegin &"# " & _
   "And (tblHoliday.HolidayDate) <=>=#" & dtmEnd &"# ) " & _
   "AND ((tblHoliday.Workday)=False) " & _
   "AND ((Format([HolidayDate],'ddd'))<>'Sat' " & _
   "And (Format([HolidayDate],'ddd')) <>'Sun'));"
      Set rst = db.OpenRecordset (strSQL)
   If rst.EOF And rst.BOF Then
     rv = 0
     Else
     rst.MoveLast
     rv = rst.RecordCount
   End If
   HolidaysOffCount = rv
End Function

So now we have all the pieces of our puzzle in place. The final step is to put them all together in a function that can be called.

You can call this function from a query or from code, pass in two dates and you'll get your answer.

Function TotalWorkDays(dtm1 As Date, dtm2 As Date) As Integer
   Dim dtmBegin As Date
   Dim dtmEnd As Date
   Dim intTotalWeekdays As Date
   Dim intHolidaysOff As Integer
   If dtm1 > dtm2 Then
      dtmBegin = dtm2
      dtmEnd = dtm1
      Else
      dtmBegin = dtm1
      dtmEnd = dtm2
   End If
   intTotalWeekdays = TotalWeekdays (dtmBegin, dtmEnd)
   intHolidaysOff = HolidaysOffCount (dtmBegin, dtmEnd)
   TotalWorkDays = intTotalWeekdays - intHolidaysOff
End Function

Our function expects two dates. Note that the function doesn't specifically ask for a begin date and an end date in a proscribed order, it simply asks for two dates and then logically determines the beginning and ending dates. To understand my philosophy and the code that handles this logic see July 07

It sets up two other variables intTotalWeekdays to hold onto the total number of weekdays in the period and intHolidaysOff to hold the number of weekdays that are not worked as a result of holidays.

The only other logic in the function is the call to the functions we wrote in our prior months

   intTotalWeekdays = TotalWeekdays(dtmBegin, dtmEnd)    intHolidaysOff = HolidaysOffCount (dtmBegin, dtmEnd)

and our final calculation of

TotalWorkDays = intTotalWeekdays - intHolidaysOff

And that readers, is the end of a long road for determining the total number of workdays between two dates.

Note that the function we created to calculate workdays relies on a holiday table in order to calculate correctly. If the first date on the calendar passed in to the function is before the first date in the table, or the latest date is beyond that last date in the table, then the function will return incorrect information. Potentially your application may give an error message or worse, silently give you incorrect information.

In an actual application, it's necessary to "trap" this error and handle it appropriately. Any time you rely on a table you should test the upper and lower bounds to make sure that the user inputs are within the expected range and if not, take appropriate steps.