DB2/400 ODBC time field to MS Access 2000 date/time field mapping error.

[Kaj Julius]

Hi all,

We want to map a file residing on an IBM eServer iSeries (formerly known as
IBM AS/400) to an MS Access 2000 table through IBM Client Access ODBC. No
problem there. But the file on the iSeries has a field that is defined as a
Time field. When this Time field is mapped to a Date/Time field, it isn't
mapped correctly. If the time field on the iSeries contains a value of, say
10.20.25, this shows up in the Access database table as 30 nov 1899
10.20.25.

The same happens when I import the file instead of linking to the external
file. When I copy the same file into a MS Excel spreadsheed with MS SQL,
there is no problem, so I assume MS Access is to blame.

Are there any patches out there that I am not aware of? Any input
appreciated. Thanks.

/ Kaj

 

[GVaught]

Speculating here:

Are you setting that fields format attribute to Time only within the design
table area of Access? If you just set the field as date/time without a
specific format, I am guessing it is making a best guess for the date as
your field does not contain this information. Your field data is only the
time value. Does that make sense?

 

[Rick Brandt]

Hi all,

We want to map a file residing on an IBM eServer iSeries (formerly known as
IBM AS/400) to an MS Access 2000 table through IBM Client Access ODBC. No
problem there. But the file on the iSeries has a field that is defined as a
Time field. When this Time field is mapped to a Date/Time field, it isn't
mapped correctly. If the time field on the iSeries contains a value of, say
10.20.25, this shows up in the Access database table as 30 nov 1899
10.20.25.

The same happens when I import the file instead of linking to the external
file. When I copy the same file into a MS Excel spreadsheed with MS SQL,
there is no problem, so I assume MS Access is to blame.

Are there any patches out there that I am not aware of? Any input
appreciated. Thanks.

Are you sure it wasn't 12/30/1899? A Time field from your AS400 has only the
time and no date. A DateTime field in Access always has both. When no date (or
a date value of zero) then Access defaults to 12/30/1899.

 

[Joe Fallon]

I would write a query off the linked table and format that field the way you
want to see it.
Access thinks it is just a time field.
Check out Access Help for how it treats date/time values.
Essentially the date is the Integer and the time is the decimal fraction of
24 hours.
So 0.5 would the 0th day at noon (.5 of 24 hours.)

 

[Kaj Julius]

Are you sure it wasn't 12/30/1899? A Time field from your AS400 has only the
time and no date. A DateTime field in Access always has both. When no date (or
a date value of zero) then Access defaults to 12/30/1899.

Yes, in our case it defaults to 11/30/1899. I don't know if it's because we
use Danish versions of both MS Access, IBM iSeries Access and OS/400.