Date conversion error - MS Access front end querying sql back end

Question

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.

Answer 1

The question is, whether your query is a Pass-Through-Query or not (the fact that the table is linked is irrelevant). If the query is Pass-Through then you must write it in the SQL-Server dialect, otherwise in Access dialect. Pass-Through is a property of the query, not a property of the table.

Another point is whether the date lies within the valid date range of the SQL-Server. These ranges are different for Access and SQL-Server:

SQL-Server
datetime (January 1, 1753 - December 31, 9999)
datetime2 (January 1, 0001 to December 31, 9999)
smalldatetime (January 1, 1900 to June 6, 2079)
date (January 1, 0001 to December 31, 9999)

Access
Date/Time (January 1, 100 A.D. -December 31, 9999 A.D.)

A date value of 0 (the default value) represents December 30, 1899 in Access and this lies outside of the smalldatetime range of SQL-Server.

The Format function does not work if the value passed to it is a String. Make sure the FromDate and ToDate variables are declared As Date in VBA or that they are variants containing a date. VarType(FromDate) should return 7 (=vbDate) in the latter case. If they are variants they should also not be Null.

Answer 2

This is a Microsoft office bug.

This problem mainly occurs when you have any "reference issue" in macro/VBA.

Follow following steps

1. Go to visual basic Editor or Press (Alt + F11 )
2. Click on menu Tools->References
3. Uncheck any library reference that starts with the word "Missing:..."
4. Now try to execute a query

Answer 3

Kevin Moore's post solved this for me. Nearly all of the PCs running my DB under Access 2016 had no issue with it, but so far two have thrown the exact error the original poster ran into. As this is years later and two Office versions later, I figured it was worth mentioning this issue still exists.

A simple DLOOKUP to read a date from a table on an SQL Server connected via the SQL Server 2012 Native Client threw the error, and removing and re-adding that specific reference fixed it (with a compile attempt that threw an error due to the missing reference performed between the removal and re-addition).

By the way, in your References window, that DLL is named "Microsoft Office XX.X Access database engine Object Library", with the "XX.X" being your Office version (16.0 for Office 2016 in my case), and the actual NAME of the DLL won't be visible due to Microsoft not having the References form large enough or sizable, and not wrapping text for the Location. Here it is selected:

Answer 4

I made a change to Access and a stored procedure at the same time, then received this error. I thought it was Access related and spent a long time with posts like this before I tested the stored proc itself. Only to find the proc was returning this exact message. Fixed the proc and of course, everything was fine.

Answer 5

I was able to solve this by simply de-compiling my application then compact and repair.