Date conversion error - MS Access front end querying sql back end
date, ms-access, sql
Solution
We had this exact same error on just one machine and solved it by removing, then re-adding our reference to DAO360.DLL (Under Visual Basic Tools->References). It never showed as MISSING.
This is definitely not a pass-through (i.e. uses a linked table), as the # date literal termination character is definitely an Access/Jet thing. ODBC Trace showed a change in the generation of the actual SQL sent to SQL Server after the "re-reference".
Problem
I am getting a date conversion error when trying to execute SQL from and an Access database against a SQL database. Our machines are all running windows 7 with office 2010. The front end is an accde. There is a search form in the database and all the fields work fine, except the two date fields. The query's where clause looks like this in the front ends VB code ``` "WHERE DrawnDate BETWEEN #" & Format(FromDate, "yyyy-mm-dd") & "# AND #" & _ Format(ToDate, "yyyy-mm-dd") & "#" ``` Based on some research, I have also tried date formats of `dd-mon-yyyy` and `dd-mm-yyyy` with no change in outcome. The expression On Click you entered as the event property setting produced the following error: ODBC -- call failed. The error received is: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/ or time from character string. (#241) The other problem is that this only happens on one PC so far and not on mine or others that I have tested.