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

user_3019786 0
Viewed 10148

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.

ms-access date sql
6 Answers

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: DAO360.DLL selected in References window

user_108861330

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.

user_1429230

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

user_147139480
Related