?
Solved

Access 2007 with SQL Server 2012 Date format problem

Posted on 2012-08-30
9
Medium Priority
?
675 Views
Last Modified: 2012-09-26
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.
0
Comment
Question by:sharpapproach
  • 5
  • 3
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38351056
<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
 

Author Comment

by:sharpapproach
ID: 38351370
Yes, but nothing works.  We have set the Property to "mm/dd/yyyy".
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38351441
"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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38351463
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
 

Author Comment

by:sharpapproach
ID: 38351544
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
 

Author Comment

by:sharpapproach
ID: 38351559
Ops... here is the screenshot
date-field-example.gif
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38351629
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
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 38353234
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38354084
<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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 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