Class: BusinessDatesTableDAO in Category General VBA/VB6 : Date and Time from Total Visual SourceBook

Business day math (taking holidays into account) with holidays stored in a Microsoft Access/Jet table using DAO for VBA and VB6 (does not support ADPs).

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the CBusinessDatesTableDAO class.
Class_Terminate Terminate Close opened objects.
Initialize Method Defines the holiday table and its field names. Use an existing table or create it. This method must be called before using any of the class methods to work with business days. Uses DAO to support ACCDB/MDB database. Does not support ADPs.
IsTable Method Determine if the table in the specified database exists.
CreateHolidayTable Private Create a table for the holiday records.
DeleteTable Private Delete the table from the database.
AddHolidayYear Method Add holiday records for the range of years to the table defined in the Initialize function.
HolidayDateFixed Method For fixed date holidays (New Year's, 4th of July, Christmas), returns the celebrated date based on US Federal guidelines. (i.e. Saturday dates are shifted to Friday, and Sunday dates are shifted to Monday).
LastDayOfWeekInMonth Private Calculates the last day (Sunday, Monday, etc.) of the month.
NthDayOfMonth Private Get the date of the Nth day (Monday, Tuesday, etc.) of the month.
AddHoliday Private Add a record to the holiday table.
IsHoliday Method Determine if the specified date is a holiday (in the holiday table).
IsBusinessDay Method Determine if the specified date is a business day (not in the holiday table and not a weekend day).
HolidayName Method Determine if the specified date is in the holiday table, and if so, provide the holiday name. Requires the holiday field name to exist with a value to retrieve.
GetNextHoliday Method Determine the next holiday date after the provided date.
GetNextHolidayName Method Get the next holiday name after the provided date.
GetNextHolidayDateName Method Get the next holiday date and name after the provided date.
GetPrevHoliday Method Determine the holiday date before the provided date.
GetPrevHolidayName Method Get the holiday name before the provided date.
GetPrevHolidayDateName Method Get the holiday date and name before the provided date.
FindHoliday Private Locates a holiday in the private holiday array.
GetBusinessDay Method Determine if a date is a business day and if not, find the next or previous business day. Weekend dates (Saturday and Sunday) are not considered business days.
MonthFirstBusinessDay Method Calculate the first business day of the month.
MonthLastBusinessDay Method Calculate the last business day of the month.
YearBusinessDays Method Number of business days in a year.
DiffBusinessDays Method Get the number of business days between two dates. The second date is adjusted to the next day to include the full day. Weekend dates (Saturday and Sunday) and holidays are not counted.
DiffWeekdays Private Get the number of weekdays between two dates. The days are rounded down (it takes 24 hours to make a day).
AddBusinessDays Method Add or subtract a number of business days to a date. Weekend dates (Saturday and Sunday) are not counted in adding/subtracting days.
BusinessDaysInMonth Method Get the number of business days in a month.
' Example of CBusinessDatesTableDAO
'
' To use this example, create a new module and paste this code into it.
' Then run either of the procedures by putting the cursor in the procedure and press:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

' Comments: Example of using the CBusinessDatesTableDAO class to create a table with holidays, read from it, and perform math based on it using VBA or VB6.
'           Uses DAO so it does not work in ADPs.
'           Two procedures are provided. The first creates the holiday table, the second performs business day math based on the values in the holiday table.

' This is the name of the holiday table to be created/used
Private Const mcstrHolidayTable As String = "tblHolidays"

Private Sub Example_CBusinessDatesTableDAO_Creation()
  ' Comments: Example of using the CBusinessDatesTableDAO class to create a file with holidays and read from it.

  Dim clsBusinessDates As CBusinessDatesTableDAO
  Dim fCreate As Boolean

  ' Instantiate the CBusinessDatesTableDAO class
  Set clsBusinessDates = New CBusinessDatesTableDAO

  ' Determine if holiday file should be overwritten if it exists
  fCreate = False
  If clsBusinessDates.IsTable(CurrentDb, mcstrHolidayTable) Then
    fCreate = (MsgBox("Do you want to overwrite the exsting holiday table?" & vbCrLf & mcstrHolidayTable, vbQuestion + vbYesNo) = vbYes)
  Else
    fCreate = True
  End If

  ' Load the holiday table. If it doesn't exist, create it
  If clsBusinessDates.Initialize(CurrentDb, fCreate, mcstrHolidayTable, "HolidayDate", "HolidayName") Then
    If fCreate Then
      ' Add holidays for the next 2 years
      clsBusinessDates.AddHolidayYear Year(Now), Year(Now) + 2
    End If
  End If

  ' Clean up by closing the class
  Set clsBusinessDates = Nothing

  ' Run the next procedure to show the business day math
  Example_CBusinessDatesTableDAO_Math

End Sub

Private Sub Example_CBusinessDatesTableDAO_Math()
  ' Comments: Example of using the CBusinessDatesTableDAO class to perform business day math based on a holiday file that was already created
  ' Uses    : mcstrHolidayTable as the name of the file containing the holidays

  Dim datDate1 As Date
  Dim datDate2 As Date
  Dim datDate3 As Date
  Dim intMonth As Integer
  Dim datFirst As Date
  Dim strName As String

  ' Peform business day math with these dates:
  datDate1 = DateSerial(Year(Now), 1, 1)        ' First day of the year
  datDate2 = DateSerial(Year(Now), 12, 31)      ' Last day of year
  datDate3 = DateSerial(Year(Now), 7, 4)        ' Fixed date holiday: July 4th

  ' Define and Instantiate the CBusinessDatesTableDAO class
  Dim clsBusinessDates As New CBusinessDatesTableDAO

  With clsBusinessDates
    ' Load the holiday table
    If .Initialize(CurrentDb, False, mcstrHolidayTable, "HolidayDate", "HolidayName") Then

      ' Example for BusinessDaysInMonth
      Debug.Print "Business Days by Month:"
      For intMonth = 1 To 12
        datFirst = DateSerial(Year(Now), intMonth, 1)
        Debug.Print "  " & Format$(datFirst, "mmm-yyyy") & ": ", .BusinessDaysInMonth(intMonth, Year(datDate1))
      Next intMonth
      Debug.Print "Total Business Days in Year: " & .YearBusinessDays(Year(Now))

      ' Example for DiffBusinessDays
      Debug.Print "Business Days " & datDate1 & " to " & datDate2 & ": ", .DiffBusinessDays(datDate1, datDate2)
      Debug.Print "Business Days " & datDate2 & " to " & datDate1 & ": ", .DiffBusinessDays(datDate2, datDate1)

      ' Example for AddBusinessDays
      Debug.Print "Add Business Days: " & datDate1 & " plus 10: ", .AddBusinessDays(datDate1, 10)

      ' Example for GetBusinessDay - Next
      Debug.Print "Business Day on or After " & datDate1 & ": ", .GetBusinessDay(datDate1, True)

      ' Example for GetBusinessDay - Previous
      Debug.Print "Business Day on or Before " & datDate1 & ": ", .GetBusinessDay(datDate1, False)

      ' Example for HolidayDateFixed
      Debug.Print
      Debug.Print datDate3 & " holiday is celebrated on: ", .HolidayDateFixed(datDate3)
      Debug.Print datDate3 & " is holiday:", .IsHoliday(datDate3)
      Debug.Print datDate3 & " is business day:", .IsBusinessDay(datDate3)
      Debug.Print datDate3 + 1 & " is holiday:", .IsHoliday(datDate3 + 1)
      Debug.Print datDate3 + 1 & " is business day:", .IsBusinessDay(datDate3 + 1)
      Debug.Print

      ' Example for next and previous holiday dates and names:
      Debug.Print "First Holiday after  " & datDate3 & ": ", .GetNextHoliday(datDate3), .GetNextHolidayName(datDate3)
      Debug.Print "First Holiday before " & datDate3 & ": ", .GetPrevHoliday(datDate3), .GetPrevHolidayName(datDate3)

      ' Example for next and previous holiday dates and names:
      Debug.Print "First Holiday after  " & datDate2 & ": ", .GetNextHolidayDateName(datDate2, strName), strName
      Debug.Print "First Holiday before " & datDate2 & ": ", .GetPrevHolidayDateName(datDate2, strName), strName

      ' Example for MonthFirstBusinessDay
      Debug.Print "First Business Day of Month " & Format$(datDate1, "mmm-yyyy") & ": ", .MonthFirstBusinessDay(Month(datDate1), Year(datDate1))

      ' Example for MonthLastBusinessDay
      Debug.Print "Last Business Day of Month " & Format$(datDate1, "mmm-yyyy") & ": ", .MonthLastBusinessDay(Month(datDate1), Year(datDate1))
      Debug.Print
    End If
  End With

  ' Clean up by closing the class
  Set clsBusinessDates = Nothing

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS