Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-11-10
6
Medium Priority
?
156 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 14

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 332 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 332 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 40

Accepted Solution

by:
PatHartman earned 1004 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 332 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 40

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

963 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