Correct Format for time that is similar to excel.

[Christopher Henderson]

I have imported as Excel spreadsheet into Access. In
Excel the field had a "time" format of hh:nn:ss
(37:30:55) which was useful for calculations greater than
24 hour time frames. When I got it into Access it would
not accept the format and would blank it out. I have
searched everywhere in the help files, but they will not
show a format greater than 24 hours. Please Help. Your
assistance is needed quite rapidly. Thanks

 

[John Nurick]

Hi Christopher,

Date/Time values represent a point in time (zero being the first instant
of 30 December 1899, 0.5 being noon that day and so on) rather than a
duration (though in practice they can often be used for the latter).

Access doesn't have an equivalent of Excel's "nn" format token. To
display date/time values greater than 1 as a number of hours greater
than 24 you'll need to use a custom VBA expression in a query or as the
controlsource in a form or report.

Something like this will do hours and minutes and can be extended for
seconds (replace XX with the name of the field:

Format(XX * 24 * 60,"00") \ 60 & ":" & Format(XX * 24*60 mod 60, "00")