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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

"Kaj Julius" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

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.)
--
Joe Fallon
Access MVP



"Kaj Julius" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

"Rick Brandt" <(E-Mail Removed)> skrev i en meddelelse
news:bqm21g$2420oj$(E-Mail Removed)...

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