[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-19
11
Medium Priority
?
3,610 Views
1 Endorsement
Last Modified: 2014-12-26
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.
1
Comment
Question by:pabrann
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 85
ID: 40270282
If you view the linked table in Design view, is Access showing it correctly as a Date field?
0
 

Author Comment

by:pabrann
ID: 40270318
I assume so. I have attached snips of the properties..Properties Format TabProperties Data TabProperties Data Tab
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 40270383
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 40

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 40270518
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
 

Author Comment

by:pabrann
ID: 40270524
Access sees this field as text. I'll try changing the driver.. Thanks!
0
 

Author Closing Comment

by:pabrann
ID: 40270553
Excellent, Thank you very much!
0
 

Expert Comment

by:Kurt Bergman
ID: 40515093
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
 
LVL 85
ID: 40516281
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
 

Expert Comment

by:Kurt Bergman
ID: 40518588
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
 
LVL 85
ID: 40518608
Sorry - I thought you were asking a question about your project. my bad ...
0
 

Expert Comment

by:Kurt Bergman
ID: 40518611
it's all fine

Happy New Year! (soon)  :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

873 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