Re: Calculating\Displaying Time over 24 hrs

Brian <noemail@xxxxxxxxxxxxx> wrote in
200832111830.171583@Brian-PC:">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

.

Relevant Pages

  • Re: Sum of Time
    ... You can't use a time field to store a DURATION, ... To store durations you need to store a numeric ... format your number to display in the hh:mm:ss format if you'd like. ... have been many posts in the past on how to do this. ...
    (microsoft.public.access.queries)
  • Re: Data Type
    ... Does SQLServer has a datatype to store directly from the above format ... Any datatype to store duration like ... Dates and times don't have any format in SQL Server. ...
    (comp.databases.ms-sqlserver)
  • Re: Storing time as integers
    ... duration, it can be used for the latter provided that each duration is less ... If you do maths on date/time values you get some strange results if you ... If you need to store durations of more than 24 hours you have little choice ... you can quite happily use a date/time data type. ...
    (microsoft.public.access.queries)
  • Re: Time Calculation For Particular Person On Particular Date
    ... I understand you have three fields: an ID number, a date and a duration. ... either you must store a start time and a finish time and calculate the ... Public Function FormatMinutes(ByVal lMins as Long) as String ... What I am trying to do is have an unbound field on the form ...
    (microsoft.public.access.formscoding)
  • RE: Trying to convert a short time field from hours to minutes
    ... then I would recommend either an integer or long integer data ... datediff("h", Time1, Time2) ... If you are trying to store a duration in a time field, ...
    (microsoft.public.access.queries)