Microsoft Access 2010 Date Picker not working for bound text box to SQL Server date field

I have a Microsoft Access 2010 application and the text box bound to a SQL Server date field will not display the date picker. If I unbind the control, it displays as it should but, when bound it does not. It looks like Access does not see the field as a date field but it is set properly in SQL Server as a Date, not Date/Time.
pabrannPresidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you view the linked table in Design view, is Access showing it correctly as a Date field?
0
pabrannPresidentAuthor Commented:
I assume so. I have attached snips of the properties..Properties Format TabProperties Data TabProperties Data Tab
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm referring to the Table that your form is bound to. Find that table in the Navigation Pane, and open it in Design view. Access will complain, but you can open it anyway.

Is InvoiceDate listed as a Date field in Access?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
Not all "date" fields in SQL Server are recognized as dates by Access.  Access may see the field as text.  Following Scott's instruction will tell you if that is the case.  To resolve the issue, you can try changing the ODBC driver you are using to link to the table.  The driver listed simply as "SQL Server" is the old version.  You will probably also see one or more versions of "SQL Server Native Client".  Mine is "SQL Server Native Client 10.0".

If switching drivers doesn't resolve the issue, see if you can encourage the DBA to change the column definition on the server.  If he won't, you'll have to constantly convert the string to a date as you use it.  

If you end up having to work with the date as a string, please complain to Microsoft.  Without user complaints, nothing changes and it is important that Access be able to properly recognize SQL Server data types.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pabrannPresidentAuthor Commented:
Access sees this field as text. I'll try changing the driver.. Thanks!
0
pabrannPresidentAuthor Commented:
Excellent, Thank you very much!
0
Kurt BergmanCommented:
I'm building an Access database (Access 2010) with SQL Server back end (2008). There are no linked tables. All forms are populated and bound on the Form On_Open. I had two forms (Parent and datasheet subform) that were not showing the date picker nor would they allow data entry. It turned out to be a data conversion in the SQL statement:
Convert(varchar,InstallDate,101) as InstallDate

Open in new window

I changed to just InstallDate and all works on the form and the datasheet subform that shares the same recordset.
Note: Format does not need to be set to a date type, just the 'ShowDatePicker' needs to be set.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
ConcordCA: Please post your own question, instead of tagging onto an existing one. Very few Experts will see your post, since this question is closed.
0
Kurt BergmanCommented:
Scott:
There was a button that said the question was still open and was encouraging to add pertinent content, as I referred to in my post.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry - I thought you were asking a question about your project. my bad ...
0
Kurt BergmanCommented:
it's all fine

Happy New Year! (soon)  :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.