none
Trying to compare passed param date to access date in SQL expression RRS feed

  • Question

  • Hi,

    I've got a table in my database that contains the UK  Public holidays for x years.  I've stored them in long date format so they're easier to read.  Then I've written the following simple routine to check a passed parameter date to that table.  It's complaining of a data type mismatch in the SQL expression.

    Public Function IsBankHoliday(DateToEvaluate As Date) As Boolean
        Dim temRS As New ADODB.Recordset
        temRS.Open "SELECT * FROM lkpBankHolidays WHERE BHDate = '" & DateToEvaluate & "';", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
        If temRS.RecordCount = 0 Then
            IsBankHoliday = False
        Else
            IsBankHoliday = True
        End If
        temRS.Close
        Set temRS = Nothing
    End Function
    

    I suspect this is because Access actually stores dates as single point numbers (whatever format you specify in the interface) but I've tried messing around with CLng etc. and I can't get it to work.  As I'm probably missing something ludicrously simple, I thought I might as well ask for help!

    Sunday, May 7, 2017 3:34 PM

Answers

  • Thanks for your reply Daniel.  That produced the same error but it's on the right lines.  I have changed the SQL to:

    "SELECT * FROM lkpBankHolidays WHERE cdate(BHDate) = '" & DateToEvaluate & "';"

    Which seems to work fine.

    • Marked as answer by MikeMay Sunday, May 7, 2017 5:20 PM
    Sunday, May 7, 2017 5:20 PM

All replies

  • Try modifying your SQL statement from

    "SELECT * FROM lkpBankHolidays WHERE BHDate = '" & DateToEvaluate & "';"

    To

    "SELECT * FROM lkpBankHolidays WHERE BHDate = '" & Format$(DateToEvaluate, "\#mm\/dd\/yyyy\#") & "';"


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Sunday, May 7, 2017 3:41 PM
  • Thanks for your reply Daniel.  That produced the same error but it's on the right lines.  I have changed the SQL to:

    "SELECT * FROM lkpBankHolidays WHERE cdate(BHDate) = '" & DateToEvaluate & "';"

    Which seems to work fine.

    • Marked as answer by MikeMay Sunday, May 7, 2017 5:20 PM
    Sunday, May 7, 2017 5:20 PM