Date field showing as text when linked from Sql Server Table

[sri123]

Hello experts, Need help asap.



I have a strange problem.

when I link a table with date field from SQL Server to Access 2013.

The date is showing as short text in access linked table.

The date picker associated to the control is also not working.

I am using ODBC driver as the DSN connection. I have changed this to Sql Server Native Client 11.0, then it worked fine. the date was pulling properly.

My application has 100 users and have checked on most of their machines there is no Sql Server Native Client 11.0 installed.

Hence, I have no other option using the ODBC driver is my DSN.

Please help how to fix this problem. All I need is when linked the date field must show mm/dd/yyyy not yyyy-mm-dd 00:00:00

Thank you in adavance

[ItsMe]

Using the DSN file as a connection, can you create a blank test DB and link to the problem table? I am thinking about creating a form and using the linked table as a RecordSource. I realize you already have this, just trying to isolate things in a control environment.

From the new form, add the field to the design surface via the Add New Fields option. save your form and add a command button. In the click event add some code to determine the data type.
msgbox VarType(controlname.Value)

I suspect the message box will display, "8".
https://msdn.microsoft.com/en-us/lib.../gg278470.aspx

If it is returning your data as a string, you can use the format function to get your string to display how you need it to. However, this is not going to guarantee your app will function correctly. For instance, what about a query that uses this field to determine a data range using the BETWEEN operator?

Might be easiest and safest to update the drivers and the connection string on each of the clients.

[pbaldy]

My guess would be that you used a newer SQL Server data type that the old ODBC driver doesn't recognize. As you've seen, switching to the native client is a solution. Probably another is changing to a different data type (datetime should work).

[Micron]

Is it possible that the regional settings where the data was input don't match where you are located?
Another possible solution might be to use the CDate function, but I don't know if this would enable the date picker on your form if you use this in the underlying query.