Converting Totals Minutes & Seconds to HH:MM:SS 1

[Tivoli0]

Hi all,

I have 2 fields in a report that total the length of all songs in a database as in the following format:

=Sum([LengthMin]): =Sum([LengthSec])

A sample result output is 350:7500 which means:
The total length of all songs is 350 minutes and 7500 seconds.

I wanted the output to show a translation of the 350 minutes as 5 hours and 50 minutes and the 7500 seconds to be calculated to - and added as well - to the minutes, so the final output should be represented as:

HH:MM:SS or in my example: 7hrs : 55min : 00sec

I tried all kind of formulas and conversions to no avail, can someone guide me please how to perform the translation?

Thanks much, -Tivoli0

 

[dhookom]

You can get this in a date/time format by:
Sum([LengthMin])/1440 + Sum([LengthSec])/86400
Then use the code on Doug Steele's site

http://members.rogers.com/douglas.j.steele/Diff2Dates.html.

This function expects two Date/Time values. Use 0 as your first date value and the expression above as the second date value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jbpez]

Try this:

=Format((((([LengthMin] * 60) + [LengthSec]) \ 60) \ 60), "00") & ":" & Format(((([LengthMin] * 60) + [LengthSec]) - ((((([LengthMin] * 60) + [LengthSec]) \ 60) \ 60) * 60 * 60)) \ 60, "00") & ":" & Format((([LengthMin] * 60) + [LengthSec]) - ((((([LengthMin] * 60) + [LengthSec]) - ((((([LengthMin] * 60) + [LengthSec]) \ 60) \ 60) * 60 * 60)) \ 60) * 60) - ((((([LengthMin] * 60) + [LengthSec]) \ 60) \ 60) * 60 * 60), "00")


John Borges

 

[Tivoli0]

Thanks for your help but still couldn't figure it out!

So, after spending 3 hrs breaking each component I found(!) the solution and now posting it for anyone who may experience the same:

The 2 fields, Sum([LengthMin]) and Sum([LengthSec]) are in the Report Footer as Text43 and Text45 respectively, so I created one text box in the Report Header of the report and put the corresponding code as the Control Source:

 =[Text45]\60\60+[Text43]\60+([Text43] [COLOR=red]Mod[/color] 60+[Text45]\60 [COLOR=red]Mod[/color] 60)\60 & ":" & ([Text43] [COLOR=red]Mod[/color] 60+[Text45]\60 [COLOR=red]Mod[/color] 60) [COLOR=red]Mod[/color] 60

the key was the usage of Mod. Thas did the trick!

The result I got was: 7:55 which means Total songs lengths is 7hrs and 55 minutes.

Thanks again for all your help!

Tivoli0

 

[GingerR]

Hi there...totally off the topic but I can't help myself....for the benefit of you and every developer that will ever maintain your db...PLEASE rename your controls to something more descriptive than 'Text43' and 'Text45'!!!

whew. I'm done )

Have a good one

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tivoli0]

Hi GingerR,

Your point is well taken!

For the sake of this thread here is the description of the 2 text boxes:

Name: Text43 (should be txtSumSumOfLengthMin)
Control Source: =Sum([SumOfLengthMin])

Name: Text45 (should be txtSumSumOfLengthSec)
Control Source: =Sum([SumOfLengthSec])

Appreciate your comment, -Tivoli0

 

[dhookom]

Thanks GingerR. I was thinking the same thing regarding the control names. Deserves a star!

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[hobman]

I know you how you can get the hrs and mins calculated but I am not sure about the seconds. to calculate the hours, =sum(value)\60

To calculate the minutes: = sum(value) mod 60

hope this helps

hobman