Time Field import Issue

[sbowman]

I have a date/time field in an Excel spreadsheet that has data such as:

245:11:21 (hhh:nn:ss format)

I am importing this spreadsheet into a holder table in Access with
datatype set to Text. I then run an append query to append to the real
table with a data type of Date/Time format hhh:nn:ss. Some values
import, others are just blanked out. How do I get these imported??

Thanks,
Shelley

 

[Van T. Dinh]

DateTime Fields in JET / Access are meant for points in date/time and NOT
for duration. Thus the time component of a DateTime value is restricted to
23:59:59.

While Access will perform type-casting to datetime correctly for values
equal or less than 23:59:59, it will reject values in text where the hours
component is 24 or greater ...

If you are using this as duration, it may be better to store the value as
multiple of the smallest time unit you require, e.g. 86400 seconds (duration
of exactly 1 day).