I am trying to create a new fuction which will work out the number of working days in a given month excluding bank holidays. Atatched is the VBA for the function, which correctly returns the number of working days, but doesn't pull back the bank holidays correctly and so returns a value of 0 for bankholidays.
The structure is as follows:
TBL015 has a configurable date field that could be set to any month.
TBL021 Has a list of all dates in the next 20 years and a mapping to it's fiscal period. (I.E the year starts April 5th)
TBL022 holds a list of bankholiday dates
QRY030 Works out the first date of each fiscal period.
QRY_TEMP Maps the date configured in TBL015 to a fiscal period using TBL021 then specifies the first day of that month using QRY030.
* I've shortened the table names to just their reference for simplicity.*
QRY_TEMP also will has an expression which calls the function, using the startdate to calculate the end of the period:
So Currently this function is set to use 1/12/2011 as startofmonth and so the end result should be 31 total days - 9 days of weekend - 2 days bankholiday. = 20 days, but the actual result returned is 22.
I think the error is probably with the ADO query but I can't spot it.
Any help you could provide would be much appreciated.
Option Compare Database
Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' This module requires references to the
' following object libraries:
' 1. Microsoft Excel X.X Object Library,
' where X.X is the Excel Version Number.
' 2. One of the following:
' For mdb files:
' Microsoft DAO 3.6 Object Library
' For ACCDB files (Access 2007):
' Microsoft Office 12 Access Database Engine Objects
' This reference should be set already.
' To set the reference, in the VBA editor:
' Tools > References.
' DAO objects:
Dim objDB As DAO.Database
Dim objQDF As DAO.QueryDef
Dim objRS1 As DAO.Recordset
Dim SSQL As String
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer, bankholidays As Integer
On Error GoTo Err_Work_Days
' Access constants:
Const strcQueryName As String = "TBL022_BankHolidays"
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
bankholidays = 0
'Get bank holidays between dates______________________________________________________________________________
SSQL = "SELECT * FROM " & strcQueryName & " WHERE (((" & strcQueryName & ".Date)>=" & BegDate & " And (" & strcQueryName & ".Date)<=" & EndDate & ")) "
' Open a DAO recordset 5 on the query:
Set objRS1 = CurrentDb.OpenRecordset(SSQL)
Do Until objRS1.EOF
bankholidays = bankholidays + 1
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
DateCnt = DateAdd("d", 1, DateCnt)
Work_Days = WholeWeeks * 5 + EndDays - bankholidays
' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates.
If Err.Number = 94 Then
Work_Days = 0
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description