CODEWITHSUNDEEP
sqldatabasems-accessms-access-2013
Adarsh Madrecha
Adarsh Madrecha

Reputation: 7886

comparison between dates not working in ms access

Using Access 2013 I have table in Access where Column "DueDate" is defined as "Date/Time" System default date format is dd/mm/yyyy

Want to change List's Row Source through VBA, based on value in a textbox "txtDueDateUpto". So, that I get all the task where the dude date is less than equal to the date entered by user in "txtDueDateUpto"

mySql = "SELECT TaskTbl.TaskID "
        & " FROM TaskTbl " _
        & " WHERE " _
        & " DueDate is not Null  and " _
        & " Format (DueDate," & """dd/mm/yyyy""" & ") <= " & Format(CDate(Me.txtDueDateUpto.Value), "dd/mm/yyyy") _

Me.listTask.RowSource = mySql

I have 3 Tasks for testing purpose. where DueDate is saved as

 TaskID DueDate
 1      25-17-2015
 2      01-07-2015
 3      29-06-2015

and, value in txtDueDateUpto is 06-07-2015 txtDueDateUpto format property is set to "Short Date" I was expecting taskID 2,3 to be returned, with the given SQL, but I am getting taskID 2

I sat all night, trying many permutations and combinations, but cannot get what is that, I am doing wrong.

newbie for Access VBA. Pls help, thanks in advance.

Upvotes: 0

Views: 2821

Answers (2)

Gustav
Gustav

Reputation: 55806

Always handle dates as dates, not strings, no exceptions.

If your textbox has been assigned a date/time format, you don't even have to use CDate or DateValue because Access then reads that value as of data type Date.

However, you must concatenate the value with the SQL code as a properly formatted string expression of the date value:

mySql = "SELECT TaskTbl.TaskID "
        & " FROM TaskTbl " _
        & " WHERE " _
        & " DueDate is not Null and " _
        & " DueDate <= #" & Format(Me!txtDueDateUpto.Value, "yyyy\/mm\/dd") & "#"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you are going to format the dates as strings -- and compare them -- then always use YYYY-MM-DD format:

   & " Format (DueDate," & """yyyy-mm-dd""" & ") <= " & Format(CDate(Me.txtDueDateUpto.Value), "yyyy-mm-dd") _

However, I'm pretty sure that MS Access can just do date comparisons without the conversion:

   DueDate <= CDate(Me.txtDueDateUpto.Value)

Upvotes: 1

Related Questions