Access Visio ODBC link fails - DATE data type

[Bob Wolf]

Access is linked to Visio. Drag a Visio shape to the drawing page (a box); Visio pops up a dialog requesting a record (select from a listing of all records in the linked table by viewing the Primary Key, such as "ID"); all related fields in the Visio shape then populate with data from from the selected record. Very impressive, really.

But in the case of DATE fields, the link fails as to all dates EARLIER than 1900. The field in the Visio shape is blank. Access is storing the dates as "7/4/1876". Date "formatting" in the linked table should not be an issue since post-1900 DATEs work fine (but I tried formatting the DATE field to Short Date anyway. Thanks for your ideas. Bob Wolf

 

[Pat Hartman]

Jet stores dates as double precision numbers (I'm sure I've type this statement 100 times in this forum). The Integer portion of the number represents the date as the number of days since Dec 30, 1899. The decimal portion represents the time of day. That makes the actual numeric value of Dec 30, 1899 = 0. Dec 31, 1899 = 1; Jan 1, 1900 = 2; Dec 29, 1899 = -1; Dec 28, 1899 = -2. The problem is with VISIO. It is not recognizing dates with serial number values less than 1 properly.

If you can link to a query rather than a table you may be able to resolve the issue by converting dates to a string. So, in the query, select all t he columns you need and format the date fields to turn them into strings.

Select ...., Format(YourDate, "mm/dd/yyyy") As FormDate, ....