Function to return True (1) if Date=Holiday Date otherwise False (0)

[Jerry Maiapu]

Hi all, here's an interesting question.

I have this function to returns true(1) if date=holidaydat e from tblHolidays otherwise false. It is not working..

Public Function IsHoliday(dtmDate As Date) As Boolean
    
    Dim db As Database
    Dim rs As Recordset
    Dim strCriteria As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(tblHolidays, dbOpenSnapshot)
    strCriteria = [HolidayDate] = dtmDate
    rs.FindFirst strCriteria
    If rs.NoMatch Then
        IsHoliday = False
    Else
        IsHoliday = True
    End If
    
    Set rs = Nothing
    Set db = Nothing

End Function

Any hint, help, assistance on how to achieve this would be appreciated..

[Delerna]

hmmm, been a while since I've done this but shouldn't
strCriteria = [HolidayDate] = dtmDate

be something more like

strCriteria = "[HolidayDate] = " & dtmDate

or since its a date is it

strCriteria = "[HolidayDate] = #" & dtmDate & "#"


stretching the memory now :)

[Jerry Maiapu]

Woops, I missed that. I suspect that must be the problem.
I'll try that

[Jerry Maiapu]

Still does not seem to work..

[Jerry Maiapu]

Hi figured it out. My fault.

table name should be surround by quotation marks

Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)

Solution:

Public Function IsHoliday(dtmDate As Date) As Boolean

   
    Dim db As Database
    Dim rs As Recordset
    Dim strCriteria As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)
    strCriteria = "[HolidayDate] = " & dtmDate
    rs.FindFirst strCriteria
    If rs.NoMatch Then
        IsHoliday = False
    Else
        IsHoliday = True
    End If
    
    Set rs = Nothing
    Set db = Nothing

End Function