Access 2007 with SQL Server 2012 Date format problem

We have an Access front end that connects to SQL.  We just upgraded from 2005 to 2012, and were happy to have a "Date" versus a "datetime" data type.  We have converted all the dates to "date" in the migration.  

However, now all the front end textboxes have format yyyy/mm/dd in the date being displayed.  

We have tried to put the Textboxes to a format of "mm/dd/yyyy", but nothing is recognized.

Help!  How do I get the Access textbox to display in "mm/dd/yyyy" with a SQL 2012 data type equal to "date"?  We don't ever need time.
sharpapproachAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
The problem is that the ODBC driver doesn't read the new date/time fields as dates but as text.
You can either convert in Access using CDate(), change the data type to DateTime in SQL Server, or try to change the driver from the old "SQL Server" to the newer Native Driver version 10 or 11.

/gustav
0
 
Jeffrey CoachmanMIS LiasonCommented:
<We have tried to put the Textboxes to a format of "mm/dd/yyyy", but nothing is recognized.>
...What do you mean by "nothing is recognized"?

What is displayed in the textbox after you change the format?
0
 
sharpapproachAuthor Commented:
Yes, but nothing works.  We have set the Property to "mm/dd/yyyy".
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Jeffrey CoachmanMIS LiasonCommented:
"Yes, but nothing works."
Again....
What do you mean by: "Nothing works"?

One more time:
"What is displayed in the textbox after you change the format?"
Nothing?
Wrong date?
Garbage?
Error message?
0
 
Jeffrey CoachmanMIS LiasonCommented:
In other words:
If you open the Linked table in Access does a date display?
When you make a form from that table a date should also display.
All you should have to do is change the Format property of the form textbox to:
    mm/dd/yyyy
change form textbox format property
0
 
sharpapproachAuthor Commented:
Sorry I am not communication well....

Yes, you are correct.  I have formatted it like you said above.  However, here is what appears in the form:

I have done some playing, and if I change the SQL field to datetime, it sees the Access textbox Format correctly.  As soon as I put it back to the date data type, it won't display it correctly.
0
 
sharpapproachAuthor Commented:
Ops... here is the screenshot
date-field-example.gif
0
 
Jeffrey CoachmanMIS LiasonCommented:
1. Then why not keep the "DateTime" datatype?
As long as you only enter dates (and not times), it should be OK

2. Try creating another textbox control and using the first control as it's source, then format the new control as: mm/dd/yyyy

3. Create a query:
SELECT fld1, fld2, YourDateField, Format([YourDateField],"mm/dd/yyy"), AS FormattedDate, cdate(Format([YourDateField],"mm/dd/yyy")) AS FormattedasRealDate

The see if any of these look the way you want.

Then create the form from this query
0
 
Jeffrey CoachmanMIS LiasonCommented:
<The problem is that the ODBC driver doesn't read the new date/time fields as dates but as text.>
Thanks Gustav, ...did not know that.

That is why I brute-forced a cdate in my post:
SELECT fld1, fld2, YourDateField, Format([YourDateField],"mm/dd/yyy"), AS FormattedDate, cdate(Format([YourDateField],"mm/dd/yyy")) AS FormattedasRealDate
...just to see if that would work...

But I guess something like this might have been better:
SELECT fld1, fld2, YourDateField, format(cdate([YourDateField]),"mm/dd/yyyy"), AS FormattedDate
FROM YourTable

Jeff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.