I have a table in which the amount of time is in seconds. In my forms I would like to display the number of seconds as hh:mm:ss. By dividing the seconds by 86400 I can convert this to hh:mm:ss but it will display the result as either short date (5 minutes 34 seconds) 00:05 or as long date 12:05:34 PM. If I change MY regional settings to 24 hour time it fixes my display when there are less than 24 hours involved but if it is more than 24 hours it will display as (26 hours 5 minutes 34 seconds) 2:05:34, even under 24 hour time. Is there a work around anyone is aware of?

The Access DateTime datatype is meant for storing a point in time, not elapsed time. Your data should be stored as an Integer of one kind or another. To display it as you require may involve a custom function. Fortunately there are many examples of how to convert to formats like these in the UA Archives and other forums (i.e. Date Time Functions) Most involve use of the MOD function. Try searching there first and let us know if this gives you what you need.

Hi
Also check out: Functions for calculating and for displaying Date/Time values in Access.

Hi niesz,
Yes, I have the time stored in seconds, the integer, which is actually an extract from a DB2 database that I am pulling into Access. I have looked through and searched the archives, and unfortunately I am unable to find anything that seems to work (maybe I'm not using the appropriate keywords?). I believe maybe a TimeValue format would be the appropriate way to go, but I am unable to find a way to get the data to format appropriately without running into the above noted scenarios when using the available control formats on the form. A function to change the format to TimeValue would be the perfect solution. If you know of one of these archived posts or where they may be available, I would sure appreciate the assistance in locating it.

Thanks!

Hi Larry,

Thanks for the reply. I have looked at this doc before and while it seems the right path, in that the resultant data of the example functions are displayed in the correct format, I am unable to get the format to apply when it is simply for a conversion of the existing control format. This article is how I was able to figure out how to do the initial conversion from seconds to hours and minutes though (<img src="/forums/images/icons/notworthy.gif"> ) 86400 seconds divisible.... so I did get some value out of it, I just simply cannot get it to modify the display as a time value versus a date stamp. Again, any help appreciated if you think I am overlooking something in this post. I'd love to solve this riddle both for my current project and for future projects.

BTW - If this helps in the solving of this, in excel, using the "[h]:mm:ss;@" format displays the data perfectly. Anyone know if there is an Access equivelant and how I can apply it?

Try:



This was derived from this post.

Thank you so much! That was Exactly what I was looking for!!!

NEWBIES: (Step by Step - using a function you created in a Module)
1. Create a new module, paste in this code, save the module as ModSecToTime (or something similar)
2. Open your form(or report), create an unbound control
3. Using the expression builder in the Control Source of the Control Properties open the expression builder (...)
4. Go to functions, underneath functions there will be "Built In Functions" and beneath that will be the name of your DB next to a folder, click on that folder
5. Choose ModSecToTime and double click.
6. In the Expression builder window, it will Enter the following function «Expr» fFormatSecondsToTime («TotalSeconds») .

----- Expr is "=" and («TotalSeconds») is the name of your seconds field ------

EXAMPLE: =fFormatSecondsToTime([SumOfSchd_Adherence])

NP.