Access 2016 date picker not shown with SQL Express (SQL Server 2008 R2 SP1)

Question

With SQL Express I've created a simple table that includes 4 columns that have Data Type as: date, datetime, datetime2(7), and datetimeoffset(7).

In Access 2016 I have a form with record source = the linked table aforementioned. The form has text boxes for each of those 4 date formats. They all show format = Short Date, and all have Show Date Picker = For dates. However, none show the date picker...

I'm also running SSMS v17.4.

How can I get the date pickers to show up? Thanks!

Answer 1

Hi Kevin,

This forum focuses on general discussion for Office 365 ProPlus. To better resolve your issue, I would move it to Access for Developers forum for more suggestion.

Thanks for your understanding.

---

Best Regards,
Winnie Liang

---

Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact [e-mail].


Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

Answer 2

Hi Kevin_R_M,

You had mentioned that,"They all show format = Short Date, and all have Show Date Picker = For dates. However, none show the date picker..."

Generally, When we set 'Show Date Picker = For dates', It will display the Date Picker.

Output:

For testing purpose, Try to create a new form and put one textbox and set 'Show Date Picker = For dates'.

Run the form and check whether it shows the date picker or not.

If it is not showing you the date picker then it is possible that something get corrupted in your database.

You can try to 'Compact and Repair' your database to check whether issue is solved or not.

Reference:

Compact and repair a database

Try to make a test on your side and let us know about the result.

We will try to provide further suggestions, If issue persist.

Regards

Deepak

 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Hi Deepak,

Thanks for your response. I created the forms with only a single textbox. First, on the table in the SQL Server. The text box does have Format = Short Date and Show Date Picker = For dates. The date picker is not shown (I'm not able to upload images until my account is verified...)

Second, on a dummy table in the Access db. The date picker is shown.

So I don't believe the problem lies in the Access db; therefore, the compact and repair option doesn't seem applicable. Please advise. Is there a reference about how a SQL server date field is supposed to work with a form in Access? Thank you!

Answer 4

Hi Kevin_R_M,

I assume that you are working with Link Table in Access.

So I try to create a Table in SQL same as yours.

I insert some dates in table.

Then I link this Table with Access. In Access It looks like below.

So here, You will notice that Access recognize 3 columns as datetime and on those columns we can see the date picker by default.

But last column recognize as short text. So because of that reason Access not showing the Date Picker.

So you can try to adjust the data types in SQL and modify it as per Access.

Then again you can try to link that table and Access will display the date picker for those fields.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi Deepak,

Thanks for another helpful response. I got this to work by doing:

1. Reset 4 date/time columns in SQL database
2. Delete linked table in Access
3. Add linked table back in
4. Check design view of linked table -- it shows Date/Time field for the SQL datetime only but that's fine now
5. Check form -- it does show date/time picker

The datetime data type with my SQL server works for me; not sure why the others don't, but it doesn't hold me up.

Answer 6

Hi Kevin_R_M,

I think that Access is not able to recognize '2018-01-25 00:00:00.0000000 +08:00' as DateTime.

It looks like it only supports the DateTime in format below.

From your last reply, It looks like if you change the DateTime format then Date Picker start displaying in Access.

So If you got the answer for your question then I suggest you to mark helpful suggestion as an answer.

It will help us to close this thread on our side.

It will also helpful for other community members in future, Who will face same kind of issue like yours.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].