Calculating\Displaying Time over 24 hrs

[Brian]

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

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

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

===========================================
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 sourcefor the text box txtTime. My issue is that I am getting a #Div/0! error inthe text box.

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

Thanks, Brian

[Rick Brandt]

Brian wrote:

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.

You would not use Larry's suggestion on a DateTime field. You should
instead be using a numeric field to store the number of minutes. THEN
Larry's suggestion would allow you to display that value in an hours:minutes
format.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Linq Adams via AccessMonster.com]

Actually, to display

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

you wouldn't want it defined as numerical; the colon between hours and
minutes would pop an error, I believe.

The point about elapsed time not being held in a Date/Time field is an
important one, though! People often get time and elapsed time confused.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200803/1

[Rick Brandt]

Linq Adams via AccessMonster.com wrote:

Actually, to display

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

you wouldn't want it defined as numerical; the colon between hours and
minutes would pop an error, I believe.

The resut of the expression would not be numerical. The field named timemin
however would be as we are performing arithmetic operations with it.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Bob Quintal]

Brian <no*****@bellsouth.netwrote in
news:200832111830.171583@Brian-PC:

>
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.

Excel isn't a database, Excel is wrong.

The problem begins when people confuse a duration, (which may be
expressed in hours:minutes) with a time which is a specific event
and also may be expressed in hours:minutes.

So you have a time when your excercise started, a second time when
your excercise finished, and a duration from the start and finish
times.

If you store the start and finish times, the duration can be
calculated as datediff("n",start,finish). You could instead store
the duration as the number of minutes. That number is the duration
you want. You can then use a custom format in a function to show it
as hh:mm or ddd:hh:mm as you wish, something like duration\60 & ":"
& duration mod 60

You could also write a custom function to parse user input of hh:nn
into minutes for storage.

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

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com