Solved

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

Posted on 2016-11-10
6
127 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 37

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 37

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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