Function to compare dates in a table

[JackC]

I am having a problem creating a function that count the number of holidays,
from a table, that appear in a date range. I have Dim'ed BegDate and EndDate
as universal variables in a separate function. I have used code that I have
seen in various other posts. Will someone please take a look at it and let
me know if you see any issues?

Public Function HolidayDays(BegDate, EndDate)
Dim OffDays2 As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
'SQL statement for count query to determine holidays between date range.
strSQL = "SELECT Count(tblHoliday.Holidate) AS CountDt FROM tblHoliday WHERE
(((tblHoliday.Holidate) Between " _
& BegDate & " And " & EndDate & "))"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
OffDays2 = rs!CountDt
Else
OffDays2 = 0
End If
HolidayDays = OffDays2

End Function

 

[Douglas J. Steele]

When working with dates in queries, you must enclose the dates in #
delimiters, and you must ensure that the dates are in a format with which
Access will not have issues. (Access doesn't respect the user's Short Date
format as defined through Regional Settings):

strSQL = "SELECT Count(tblHoliday.Holidate) AS CountDt " & _
"FROM tblHoliday WHERE " & _
"Holidate Between " & _
Format(BegDate, "\#yyyy\-mm\-dd\#") & _
" And " & Format(EndDate, "\#yyyy\-mm\-dd\#")

(Note that I tidied up the SQL by removing some of the unnecessary
parentheses Access is prone to insert)