UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Converting Seconds, Summing, Office 2007    
 
   
danbouton
post Apr 29 2010, 09:41 AM
Post #1

UtterAccess Enthusiast
Posts: 74
From: Indiana
I know this is another of those elementary things I am just missing.

In the detail section of my report I have a field [Duration]. Duration represents seconds.
In the report footer I have a the sum of Duration, or =Sum([Duration]).

I would like to add in the Detail section of the report an expression which will take [Duration] and convert it to hh/mm/ss.
I would like to add in the Report Footer section of the report an expression which will take my sum of [Duration] and convert it to hh/mm/ss.... keeping in mind, this may be over 24 hours.

Suggestions?
Go to the top of the page
 
+
RAZMaddaz
post Apr 29 2010, 10:20 AM
Post #2

UtterAccess VIP
Posts: 6,328
From: Bethesda, MD USA
Here is a Link to a Microsoft Function called "On Time and How Much has Elapsed." Copy this function into your Modules area. Then in your Detail area type the name of the Function around your Field, like the following:

=HoursAndMinutes([Your Field Name]) ----Make sure you include the equals sign

The above will convert the number in Your Field to hours and minutes.

Then in the Report Footer, type the following:

=HoursAndMinutes(Sum([Your Field Name])) ----Make sure you include the equals sign

You should use the same Field as in the First formula.

Here is a link to the MS function. Hour And Minutes function
Go to the top of the page
 
+
rbianco
post Apr 29 2010, 10:26 AM
Post #3

UtterAccess VIP
Posts: 1,730
From: Carrollton, TX
I wanted the excercise and came up with this duration function:

CODE
Public Function MyDuration(MySeconds As Long) As String
Dim iHours As Long
Dim iMinutes As Integer
Dim iSeconds As Integer

iHours = Fix(MySeconds / 3600)
iMinutes = Fix((MySeconds - (iHours * 3600)) / 60)
iSeconds = (MySeconds - (iHours * 3600)) - (iMinutes * 60)

If iHours > 0 Then
    MyDuration = CStr(iHours) & ":"
Else
    MyDuration = "0:"
End If

If iMinutes > 0 Then
    MyDuration = MyDuration & Right("0" & CStr(iMinutes) & ":", 3)
Else
    MyDuration = MyDuration & "00:"
End If

If iSeconds > 0 Then
    MyDuration = MyDuration & Right("0" & CStr(iSeconds), 2)
Else
    MyDuration = MyDuration & "00"
End If
End Function
Go to the top of the page
 
+
doctor9
post Apr 29 2010, 10:28 AM
Post #4

UtterAccess VIP
Posts: 9,430
From: Wisconsin
Apparently it's a slow day here at UA. (IMG:style_emoticons/default/iconfused.gif) Here's my take on it:

CODE
Public Function SecsToTime(lngSeconds As Long) As String

    Dim intDays As Integer, intHrs As Integer, intMins As Integer

'   60 Secs per min
'   3600 Secs per hour
'   86,400 Secs per day

    intDays = Int(lngSeconds / 86400)
    lngSeconds = lngSeconds - (intDays * 86400)
    
    intHours = Int(lngSeconds / 3600)
    lngSeconds = lngSeconds - (intHours * 3600)
    
    intMins = Int(lngSeconds / 60)
    lngSeconds = lngSeconds - (intMins * 60)
    
    SecsToTime = ""
    
'   Convert days to hours
    If intDays > 0 Then
        intHours = intHours + (24 * intDays)
    End If
    
    SecsToTime = Format(intHours, "00") & "/" & Format(intMins, "00") & _
                                                    "/" & Format(lngSeconds, "00")

End Function

Hope this helps,

Dennis
Go to the top of the page
 
+
theDBguy
post Apr 29 2010, 12:11 PM
Post #5

Access Wiki and Forums Moderator
Posts: 48,595
From: SoCal, USA
Hi All,

Sounds like fun. Here's my "lazy" take on it:

CODE
=[Duration]\3600 & ":" & ([Duration] Mod 3600)\60 & ":" & [Duration] Mod 60


Cheers (IMG:style_emoticons/default/cheers.gif)
Go to the top of the page
 
+
danbouton
post Apr 29 2010, 12:28 PM
Post #6

UtterAccess Enthusiast
Posts: 74
From: Indiana
QUOTE (rbianco @ Apr 29 2010, 11:26 AM) *
I wanted the excercise and came up with this duration function:

CODE
Public Function MyDuration(MySeconds As Long) As String
Dim iHours As Long
Dim iMinutes As Integer
Dim iSeconds As Integer

iHours = Fix(MySeconds / 3600)
iMinutes = Fix((MySeconds - (iHours * 3600)) / 60)
iSeconds = (MySeconds - (iHours * 3600)) - (iMinutes * 60)

If iHours > 0 Then
    MyDuration = CStr(iHours) & ":"
Else
    MyDuration = "0:"
End If

If iMinutes > 0 Then
    MyDuration = MyDuration & Right("0" & CStr(iMinutes) & ":", 3)
Else
    MyDuration = MyDuration & "00:"
End If

If iSeconds > 0 Then
    MyDuration = MyDuration & Right("0" & CStr(iSeconds), 2)
Else
    MyDuration = MyDuration & "00"
End If
End Function


I used this one. Thank you to all. I created a module and named it Mod_MyDuration. This was a great find! Thank you. This piece of code is going in my collection.
Go to the top of the page
 
+
danbouton
post Apr 29 2010, 12:29 PM
Post #7

UtterAccess Enthusiast
Posts: 74
From: Indiana
QUOTE (theDBguy @ Apr 29 2010, 01:11 PM) *
Hi All,

Sounds like fun. Here's my "lazy" take on it:

CODE
=[Duration]\3600 & ":" & ([Duration] Mod 3600)\60 & ":" & [Duration] Mod 60


Cheers (IMG:style_emoticons/default/cheers.gif)


I like this one too... I just dont want to remember it all the time. It's the only reason I went with the function instead.
Go to the top of the page
 
+
RAZMaddaz
post Apr 29 2010, 12:31 PM
Post #8

UtterAccess VIP
Posts: 6,328
From: Bethesda, MD USA
(IMG:style_emoticons/default/cryhard.gif)
Go to the top of the page
 
+
theDBguy
post Apr 29 2010, 12:54 PM
Post #9

Access Wiki and Forums Moderator
Posts: 48,595
From: SoCal, USA
QUOTE (danbouton @ Apr 29 2010, 10:29 AM) *
I like this one too... I just dont want to remember it all the time. It's the only reason I went with the function instead.

Hi,

I told you I was lazy. If I have to convert it to a function, here's what it might look like:

CODE
Public Function MyDuration(Duration As Long) As String
     MyDuration = Duration\3600 & ":" & (Duration Mod 3600)\60 & ":" & Duration Mod 60
End Function


Note: I'm still doing it the lazy way - without error checking.

Cheers.
Go to the top of the page
 
+
rbianco
post Apr 29 2010, 01:14 PM
Post #10

UtterAccess VIP
Posts: 1,730
From: Carrollton, TX
While I "technically may have "won" (IMG:style_emoticons/default/grinhalo.gif) I will always bow down to one liners (IMG:style_emoticons/default/notworthy.gif) Well done, you dastardly dbGuy.

I am so rudimentary in my approach, needing to "see" every little step occur in my code, that I currently lack the faith <read: skill (IMG:style_emoticons/default/wary.gif) > to come up with such a streamlined solution.
Go to the top of the page
 
+
theDBguy
post Apr 29 2010, 01:22 PM
Post #11

Access Wiki and Forums Moderator
Posts: 48,595
From: SoCal, USA
QUOTE (rbianco @ Apr 29 2010, 11:14 AM) *
While I "technically may have "won" (IMG:style_emoticons/default/grinhalo.gif) I will always bow down to one liners (IMG:style_emoticons/default/notworthy.gif) Well done, you dastardly dbGuy.

I am so rudimentary in my approach, needing to "see" every little step occur in my code, that I currently lack the faith <read: skill (IMG:style_emoticons/default/wary.gif) > to come up with such a streamlined solution.

Thanks, rbianco.

I don't see anything wrong with your code or Dennis'. It's easier to understand than mine. If I wasn't lazy and added the error checking and comments in my code, I may end up with a longer function than yours.

Cheers (IMG:style_emoticons/default/cheers.gif)
Go to the top of the page
 
+
« Next Oldest · Access Reports · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th June 2013 - 08:23 PM