Holiday Days between Date Range Code not working quite right.

Posted on 2005-04-20
Medium Priority
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
  • 2
  • 2
LVL 17

Accepted Solution

Arji earned 375 total points
ID: 13829089
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.

Expert Comment

ID: 13830268
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

ID: 13833973
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

ID: 13834422
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

Expert Comment

ID: 13834991
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

840 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