Access frontend, SQL backend causing date issues

Question

Hi all,

Spent hours googling/binging this, but never seem to quite get the right answer, so I hope someone out there can help me?

I recently migrated tables from my access database to sql 2008R2 using SSMA. All went well and the access tables were linked to the new sql versions. The problem is that although the SQL tables have dates set as 'DataTime', Access appears to see it as text (when I open the access table property). This is causing an issue in the forms and queries where a date is presented as 2011-02-03 (yyyy-mm-dd) rather than 03-02-2011 (dd-mm-yyyy). Setting the format property in a form seems to have no effect.

Any ideas? Any complicated fix, I might need in a step by step guide. Thank you in advance.

Regards

Paladin

Answer 1

See solution (for me) on http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/7305ad70-0084-43ec-8379-532d430da9d4

The problem may be related to Regional Settings and how the ODBC-driver(s) handle this - or do not handle...

Answer 2

• did you find any solution?

Same problem here, and also a bit of googling... On top of the display it also gives error when changing a date and the form try to update the SQL-field in backend...

Answer 3

Try reading this first - it might shed some light on your situation:

http://allenbrowne.com/ser-36.html

---

Bob Larson, Former Access MVP (2008-2010) http://www.btabdevelopment.com (free Access tools, tutorials, and samples)

Answer 4

OK, starting to wonder if i'm doing something stupid now. If I create a query using "Expr1: Format([Date Field],"dd/mm/yyyy")" it works and I get a value of 18/7/2010. Try doing something like this in a form and it's a no go. It always shows as 2010-07-18 00:00:00, but the query "expr1" shows fine. Not convenient to create queries for all the date fields as this is an event survey db that has loads of dates and times for various things.

Baldness is sure to follow soon!