Holiday Days between Date Range Code not working quite right.

Posted on 2005-04-20
Last Modified: 2012-06-21

I am creating a database report that will determine if the days between the "opentime" & "closetime" of a helpdesk problem ticket was closed within 3 days (eliminating weekends and holidays)

I have the following Public Functions in a module

Public Function IsHoliday(dtmDate As Date) As Boolean

'Created By:        Robert L. Johnson III
'Mod Date:          February 19, 2003
'Purpose:           Determine if the date provided is a Holiday
'                   NOTE: This function requires a table called tblHolidays with one field called
'                   HolDate which is a date/time field and includes all the dates you consider
'                   holidays
'In:                dteDate is the date to be checked
'Out:               Returns either True if the date is found in the table (a holiday) and False
'                   if the date is not found in the table
'Example:           IsHoliday(#1/1/03#) returns True (New Year's Day)
    Dim db As Database
    Dim rs As Recordset
    Dim strCriteria As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)
    strCriteria = "[HolDate] = #" & dtmDate & "#"
    rs.FindFirst strCriteria
    If rs.NoMatch Then
        IsHoliday = False
        IsHoliday = True
    End If
    Set rs = Nothing
    Set db = Nothing

End Function


Public Function CountHolidays(dtmStartDate As Date, dtmEndDate As Date) As Integer

'Created By:        Robert L. Johnson III
'Mod Date:          February 19, 2003
'Purpose:           Determine the number of holidays between two dates
'In:                dteStartDate is the first date, dtmEndDate is the last date
'Out:               Returns the number of holidays between start and end date
'Example:           CountHolidays(#12/31/02#, #1/3/03#) returns 1
'                   (1/1/01 is a holiday (New Year's Day))

    Dim intHolidayCount As Integer
    Dim intDaysBetweenDates As Integer
    Dim i As Integer
    Dim dtmTemp As Date
    If dtmStartDate > dtmEndDate Then
        dtmTemp = dtmStartDate
        dtmStartDate = dtmEndDate
        dtmEndDate = dtmTemp

    End If
    intHolidayCount = 0
    intDaysBetweenDates = dtmEndDate - dtmStartDate
    For i = 0 To intDaysBetweenDates
        If IsHoliday(DateAdd("d", i, dtmStartDate)) = True Then intHolidayCount = intHolidayCount + 1
    Next i
    CountHolidays = intHolidayCount

End Function

I am calling the CountHolidays function within a query that the report uses, I will show examples below after listing definitions of fields in the query that are effected

"opentime" - field listing Date/Time (General Date) that ticket was opened
"closetime" - field listing Date/Time (General Date) that ticket was closed

03/17/05 is a added holiday date in the Holdays table for testing

If I enter the following items in the query it gives me all zeros(1st choice being what I want)

Holidays:CountHolidays([opentime],[closetime]) (*several tickets have 03/17/05 in range*)

But if I enter the following item in the query it works


It seems to have a problem when I try to have the 1st date as anything other than a hard coded date.  I have verified that the "opentime" field is formatted correctly plus it works in all other date calculations.

This is driving me nuts please help!
Question by:nedbeam
    LVL 17

    Accepted Solution

    Sounds like you maybe nuking your dtmStartDate somehow. Maybe create some static variables to assign the dates to and use them in your calcs. Also, I would never allow dtmStartDate to be >= than dtmEndDate in the first place.  I notify the user that it is invalid in the calling sub.  That way you won't need to swap the dates around and avoid possibly nuking one of them.  Otherwise, I don't see any other possibility of you nuking your values.
    LVL 4

    Expert Comment

    I copied and pasted the functions in a DB and they seem to work OK.  I tried CountHolidays in VBA and in a query.

    I built a table tblHolidays with one holiday 3/17/05 and a table tblTimes with 1 record with [opentime] = 3/16/2005 and [closetime] = 3/18/2005.

    The following query

    SELECT tlbTimes.opentime, tlbTimes.closetime, CountHolidays([opentime],[closetime]) AS Expr1
    FROM tblHolidays, tlbTimes;

    gives me

    opentime      closetime      Expr1
    3/16/2005      3/18/2005      1

    You might want to look at the query to see if there's a problem related with the field opentime.

    Author Comment

    Yes indeed if I create a new table with opentime & close time it works!  Which made me dig deeper into what is the issue with opentime.  Well I figured out what the problem is but do not understand why it is not working.  I found if opentime is set to general date format and has  time entered also it kills the calculation, but it is ok if close time has the time entered also.

    Any ideas why and how to get it to work with the date & time also, because I need that for calculation purposes.

    Author Comment

    I was able to get it to work, my solution may not be the best but it worked.  In the query I added the following entry

    opendate:DateValue([opentime]) *This pulled just the date out of the Date/Time value of opentime

    I then used the "opendate" in my "Countholidays" function instead of "opentime" and it now works!  I then in turn subtract the "Countholidays" answer from my days between calculation!

    Thanks All for the help
    LVL 4

    Expert Comment

    I think your solution of DateValue() would be the best one for this function.

    You may have already figure this out.  The reason why the inclusion of time did not work with the query is because when you pass a specific time with the date into the IsHoliday function, the strCriteria will not find that specific time since the holidays are created as dates without time modifiers.

    The inclusion of time in the dtmEndDate parameter did not matter since it was only used to create the intDaysBetweenDates counter which.

    If you need to preserve the time portion, you might want to use a query with the BETWEEN clause to specify a range of dates.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now