Calculate time

[Gerfield]

Hi Guys,

I am sorry if this is a simple question but I have not been able to figure
it out so I would appreciate some help.... I want to be able to provide a
total of a time column in a form. What I have is a form with columns of
data, one of which is time in hh.mm.ss format and I would like to be able to
show a total for this column (and eventually others) in the footer of the
form (and eventually a report).

Any suggestions will be greatly appreciated.

 

[Rick B]

There is no such thing as a time in hh:mm:ss in Access the way you are using
it. TIME is a point in time. (It is 2 o'clock.) DURATION is a length of
time. (That took 2 hours and three minutes).

You can't total TIMES. 2 o'clock PLUS 3 o'clock equals what?

DURATIONS need to be stored as number. You could display these in three
separate fields to indicate hours, minutes, and seconds. You could even
build a control that would display it in the format you mention using some
division, BUT there is still a NUMBER somewhere that equals the number of
seconds. THAT field could be used to perform math.

You need to rethink your structure if you want to perform math on a DURATION
and make sure the data is stored as a number.

 

[John Vinson]

Hi Guys,

I am sorry if this is a simple question but I have not been able to figure
it out so I would appreciate some help.... I want to be able to provide a
total of a time column in a form. What I have is a form with columns of
data, one of which is time in hh.mm.ss format and I would like to be able to
show a total for this column (and eventually others) in the footer of the
form (and eventually a report).

Any suggestions will be greatly appreciated.

Access Date/Time values are optimized to display *a precise point in
time*, and aren't really appropriate for storing durations. For one
thing, though you can sum them, any time summing to over 24 hours will
roll over into another day - you cannot (easily) display 25 hours as
such, only as #31 Dec 1899 01:00:00#, because of the way date values
are stored.

I'd suggest storing your time duration in a Long Integer field of
seconds; you can format it for display with an expression like

[Dur] \ 3600 & Format([[Dur] \ 60 MOD 60, "\.00") & Format([Dur] MOD
60, "\.00")


John W. Vinson[MVP]

 

[Pedro]

Hi,

I had a problem with weektotals getting above 24 hrs.
And found the solution from Jon K for my problem here:
http://www.access-programmers.co.uk/forums/archive/index.php/t-84921.html

I changed it a little bit and put it in the control source of an unbound
textbox in the footer:
=Int(Sum([Daytotal]))*24+DatePart("h",Sum([Daytotal])) &
Format(Sum([Daytotal]),":nn")
As you'll notice I replaced the intervals [dtmEnd]-[dtmStart] with my
Daytotal

I really hope this will work for you as well!

Pedro