Displaying seconds into HH:MM:SS

logonlarry used Ask the Experts™
Hi all,

I have a field called elapsed time. It displays the value in seconds. In my report, all I want to do is display in Hours, Minutes, Seconds (ie HH:MM:SS). This must be easy but I am new to Access.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

divide it by 3600
add ":"
get remainder for division by 3600 and divide it by 60
add ":"
get remainder for division by 60

ad you are done :)

You can also see at http://office.microsoft.com/en-us/access/HA011102181033.aspx
and if you like do dig deeper: http://msdn.microsoft.com/en-us/library/dd569711.aspx - It's really useful.
Set the format property of the textbox to:
If in a query or code use
=Format("[elapsed time]", "hh:nn:ss")

Note: format does not change the stored value. It only changes the way it is displayed.
Oops, I forgot. Internally in Access time is measured in days not seconds. So for the textbox put
in the format property and put
=[elapsed time]/86400
in the control source.

In a query or code use
=Format([elapsed time]/86400, "hh:nn:ss")
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.


Thanks Nelson I've done this, however one particular total of the time is greater than 24hrs and does not display correctly.


Thank you, after a bit of tweaking I got it to work.
In that case, yaw want to use the technique suggested by gtworek.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial