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

  • Thread starter Thread starter Kaj Julius
  • Start date Start date
K

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
 
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?
 
Kaj Julius said:
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.
Click to expand...

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.
 
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.)
 
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.
Click to expand...

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.
 
Back
Top