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

Have you tried to set your default date time to be in the format yyyy-mm-dd, then use CDate( field_of_string_expression )  to get the data as Date instead of String, save that data (it will be internally stored as number of days since December 30th, 1899, independantly of the user default format for dates), then, bring back your default date format. Note that CDate check the default date format to know what date is really meant by:  01-02-03, as example,  so that is why you have to have your default matching the one of your strings representions of date. It is not nice to impose that procedure on a typical end user, though, so, if this process has to be performed many times, routinely, this solution is not appropriate.

(We can just hope that one day VBA would be able to create a 'context' of default format, on which we can then apply formatting functions, like C# can, so we could handle these cases of mismatched date formats, and of dot or coma for decimal, and so on, WITHOUT having to PHYSICALLY change the User Setting... but I doubt it would ever be so, for VBA that is.)

Answer 2

I'm using the Windows 7 SQL driver (WDAC I believe). Date is right aligned, so I guess it's seeing it as a date field. I have tried the Access frontend on a couple of sytems (all win 7, but a mix of office 2007 and 2010). same result.

Answer 3

Thanks, John. I was afraid of that. I first thought of IsDate(), but I checked and that would identify a string.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 4

Unfortunately, DatePart will handle date strings - auto converts them for the purposes of the function.

If the field is left-aligned when looking at it in a table or query, then it is being seen as a text (string) field.  If it is right-aligned, it is being seen as a date field.

How are you linking to the SQL Server tables?  DId you use ODBC to link to the tables?   IF so, which driver did you use - SQL Native Client, SQL Server, something else?

---

John Spencer Access MVP 2002-2005, 2007-2011 The Hilltop Institute University of Maryland Baltimore County

Answer 5

How a date is presented is a result of regional settings. Try using a Date function like DatePart on the the field. For example: DatePart("d",field) should return the correct day. If it does, then it is seen as a Date/Time datatype. And the problem is a formatting issue.

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries