Re: Calculating\Displaying Time over 24 hrs

So with this should I input as =[time]\60 & ":" & [time] MOD 60 in my control source?
When I put this in as my control source, it displays 7:28 as 0:9.

Brian

If you assume you decide to keep your time in minutes and the variabe is timemin, but want to show it in hours,

timehrmin = timemin\60 & ":" & timemin MOD 60

I tried that formula in the Immediate Window, using 72 as the accumulated number of minutes

? 72\60 & ":" & 72 MOD 60
1:72

or for 27 hours and 35 minutes (1655 minutes)

? 1655\60 & ":" & 1655 MOD 60
27:35

Larry Linson
Microsoft Office Access MVP

"Brian" <noemail@xxxxxxxxxxxxx> wrote in message
news:200832111830.171583@xxxxxxxxxxx

I have a 2000/2002 Access db that I use to collect and store my exercise time using a form to enter. I wanted to see
a summary of the total time for each exercise so I have a subform that does this. Only issue is that when I go over
24 hrs I get the infamous summing time issue. It would be really easy if Access used the Excel [h]:nn format, but it
doesn't. Why can't this be used in Access, Microsoft?????????????????..anyway I digress.

I have two text boxes, one for the detail called txtTime and one for the Summary called txtTotalTime. txtTime
control source would normally be the data field Time and txtTotalTime is just =Sum([Time]).

However, since it does not add up correctly, I added this code as a Public Function.

===========================================
Public Function TotalTimeDisp(lngTotTime As Long) As String

Dim intHours As Integer Dim intMinutes As Integer

intHours = Int(lngTotTime / 60) intMinutes = lngTotTime Mod (intHours * 60)

TotalTimeDisp = Str(intHours) & ":" & Trim(Format(intMinutes, "00"))

End Function ===============================================

and use =TotalTimeDisp(Datepart("n",[total time])) as the control source for the text box txtTime. My issue is that
I am getting a #Div/0! error in the text box.

I would so like to have an easy resolution for this, but I guess my first question is, what format do I need to store
the time in. They are in Date\Time right now. Do I need to change this to text or a number? Any help would be
appreciated.

Thanks, Brian

.

Relevant Pages

  • Re: CalculatingDisplaying Time over 24 hrs
    ... one for the detail called txtTime and one for the ... Summary called txtTotalTime. ... Public Function TotalTimeDispAs String ... Dim intHours As Integer Dim intMinutes As Integer ...
    (comp.databases.ms-access)
  • CalculatingDisplaying Time over 24 hrs
    ... one for the detail called txtTime and one for the Summary called txtTotalTime. ... txtTime control source would normally be the data field Time and txtTotalTime is just =Sum. ... However, since it does not add up correctly, I added this code as a Public Function. ... Dim intHours As Integer Dim intMinutes As Integer ...
    (comp.databases.ms-access)
  • Re: Public Function to display in text box
    ... I entered that into the control source property and that produces the #Name? ... The above is what goes in the control source for the textbox. ... Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia ... Public Function GetCurrentWork_GPCIAs Double ...
    (microsoft.public.access.formscoding)
  • Re: Using Global Variables
    ... Public Function GetAgentName() As String ... in the "Main" form I have a control that opens a report which also has ... a textbox with =GetAgentNameas control source. ...
    (microsoft.public.access.formscoding)
  • Re: Public Function to display in text box
    ... I entered that into the control source property and that produces the #Name? ... The above is what goes in the control source for the textbox. ... Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia ... Public Function GetCurrentWork_GPCIAs Double ...
    (microsoft.public.access.formscoding)