Solved

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

Posted on 2016-11-10
6
124 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 13

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 36

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 50

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
 
LVL 1

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 36

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

756 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