Solved

Microsoft Access Can't Resolve Dates in SQL Server 2012 Tables

Posted on 2016-11-10
6
91 Views
Last Modified: 2016-11-11
I have a Microsoft  Access 2010 Frontend app that is connected to a local copy of SQL Server 2012 Express. There seems to be an incompatibility with the date data type in SQL Server. The SQL db stores its dates in this format: yyyy-mm-dd  . There is a Date of Birth field (DoB) in one of the SQL tables and the data type is set as 'date'. I realised that Access is confused with this field.

First, I designed a form to display the date in [DoB] in the format: dd-mmm-yyyy. I did this by setting the format property of the [DoB] field in the form's design view. But Access ignores this and still displays the date as yyyy-mm-dd.


I then decided to test the date in [DoB] against the present date [txtPresentDate] to see which was greater. As you'll see in the attached images, Access said the date in [DoB] was greater than present date even though the date in [txtPresentDate] is clearly greater than that of [DoB].


I also specified (in the form's design view) that Access should display the date picker control whenever the [DoB] field is clicked. It also ignored this. I then set a Macro to display the date picker whenever the [DoB] field is clicked. I then got a Macro Error 2046.


The Access 2010 Error Numbers & Description PDF describes Error 2046 as :

The command or action '|1' isn't available now.@* You may be in a read-only database or an unconverted database from an earlier version of Microsoft Access. * The type of object the action applies to isn't currently selected or isn't in the active view.@U


How do I remedy this?
Date-Error-1.JPG
Date-Error-Macro.JPG
0
Comment
Question by:Theo Fitchner
6 Comments
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 83 total points
ID: 41882752
Here's the thing - SQL Server doesn't store the date as a date - it's actually an integer!

When representing the data as part of a results of a query, it converts the integer representation to a date.

When you load data for your page using T-SQL queries, can you convert the date using the CONVERT function in your SELECT statement? Let the Access front-end determine the regional setting in which the date should be represented.

My vote would be to always CONVERT and process as ISO dates (format specifier for CONVERT = 112). For more on CONVERT, refer: https://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 83 total points
ID: 41882760
First, SQL does not store the date in format "yyyy-mm-dd" it stores it as an integer (number of days since 1900-01-01) and the application (Access, SSMS, etc.) displays it as "yyyy-mm-dd".  You are likely confusing the display value with the data value or you are treating it as a string instead of a date.
1
 
LVL 34

Accepted Solution

by:
PatHartman earned 251 total points
ID: 41882832
Access displays dates either in the default format defined by Windows or in the specific format you defined for a control or table column.  If you are seeing a date stored in SQL Server displayed as yyyy-mm-dd and that is not your normal display format then Access is almost certainly "seeing" that date as a string and that is why your compare is not working.

Depending on what version of the ODBC driver you are using, Access may not recognize certain SQL Server date formats as datetime fields.  The only SQL Server datatype that works in all versions of the ODBC driver is DateTime.  Date or DateTime2 as well as the other data types are not seen by Access as dates.  Try installing the "ODBC Driver 11 for SQL Server".  That is the one that I found to work best.  The "SQL Server" driver, which is the default installed with Access, is compatible with SS 2000 and anything defined after that version of SQL Server causes a problem.

If the new driver doesn't work for you, get your DBA to either change the field's data type (if he will) or create a view for you that does the necessary conversion to the DateTime data type.

Looking at the picture, I would guess that the SS definition is Date since DOB does not need to include a time element.  Date takes less space to store than DateTime so in the absence of any other requirement, the DBA would choose the most efficient data type.  Hopefully, he will change it or create a view for you.  If not, you will need to use the cDate() function in your queries to convert it to a date on the fly.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 83 total points
ID: 41883486
Access should display the date picker control whenever the [DoB] field is clicked. It also ignored this.

Then the field is not seen in Access as data type Date. As Pat mentions, it is most likely converted by the ODBC driver to text.
If you can't have the field changed to DateTime, modify the query that drives the form:

    Select *, DateValue([DoB]) As TrueDoB From YourTable

/gustav
0
 

Author Closing Comment

by:Theo Fitchner
ID: 41884190
I didn't know that's how SQL Server stored dates. Pat's suggestion of setting the [DoB] field to DateTime did the trick. Access now recognises it as a date field. I've been using MS Access for years. This is my first project with MS SQL. Thanks very much Nakul, Brian and Gustave.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41884206
SQL Server stores dates the same way that Access stores them.  Just the origin point is different.  Access uses double precision.  The integer is the number of days since 12/30/1899 (a negative number is days before so -1 is 12/29/1899, 1 is 12/31/1899) and the decimal is the time since midnight.  so 1.5 would be noon on 12/31/1899.  I don't remember the SQL Server origin date.  It is either one day before or one day after the date Access uses for the 0 value.

Other relational databases and even Excel use similar techniques.  The point is that a numeric representation is cleaner for sorting, comparing, and date arithmetic.  The dates are converted to a human readable format for display only.  Internally, dates are always double precision numbers.

I see that you are someone who rewards participation regardless of who actually solved your problem.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now