Time Conversion

[Timothy]

I am trying to convert a field that has a time value stored in millieseconds
to format like hh:nn:ss. I thought this would be easy, but I can't seem to
make anything work properly. The field is a long integer (ie. 115590). Any
help would be greatly appreciated.

 

[Guest]

Dates and Times in Access/VBA is stored as numeric values, with one day equal
to a value of 1.0. Fractions of days are decimal values. So to convert from
your milliseconds to a valid time value:
TimeValue:[milliseconds]/(24*60*60*1000)
i.e 24 hours/day * 60 min/hr * 60 sec/min * 1000 msec/sec gives the number
of milliseconds in a day
This gives the numeric value, if you now format it as a time you will see
the equivalent hh:mm:ss
ShowTime:Format([TimeValue],"hh:nn:ss")
You can put these two together:
ShowTime:Format([milliseconds]/(24*60*60*1000),"hh:nn:ss")

 

[Timothy]

That was exactly what I needed. Thanks so much. I was trying to do that
same thing but in a giant expression that I couldn't get to work.

Dates and Times in Access/VBA is stored as numeric values, with one day equal
to a value of 1.0. Fractions of days are decimal values. So to convert from
your milliseconds to a valid time value:
TimeValue:[milliseconds]/(24*60*60*1000)
i.e 24 hours/day * 60 min/hr * 60 sec/min * 1000 msec/sec gives the number
of milliseconds in a day
This gives the numeric value, if you now format it as a time you will see
the equivalent hh:mm:ss
ShowTime:Format([TimeValue],"hh:nn:ss")
You can put these two together:
ShowTime:Format([milliseconds]/(24*60*60*1000),"hh:nn:ss")

I am trying to convert a field that has a time value stored in millieseconds
to format like hh:nn:ss. I thought this would be easy, but I can't seem to
make anything work properly. The field is a long integer (ie. 115590). Any
help would be greatly appreciated.

 

[Guest]

Format(CDate(115590 * 0.00000001157474074074), "HH:NN:SS")

 

[John Vinson]

I am trying to convert a field that has a time value stored in millieseconds
to format like hh:nn:ss. I thought this would be easy, but I can't seem to
make anything work properly. The field is a long integer (ie. 115590). Any
help would be greatly appreciated.

Access Date/Time values cannot display time to any finer accuracy than
integer seconds.

If you just want to DISPLY your data as hh:nn:ss.sss (to the
millisecond), try an expression like

[timevalue] \ 3600000 & Format([timevalue] \ 60000 MOD 60, ":00") &
Format(([timevalue] - 60000 * [timevalue] \ 60000)/1000, ":00.000")


John W. Vinson[MVP]