Time grand total in MS-Access

[simondsm]

I have a video tape library that we use at work here in a MS-Access
database.

When originally desgined, we never thought ot convert the time on the
fly into total seconds, but instead, we stored all of the times in
hh:nn:ss format - because that is the way we thought about things
around here, and which would make sense in our line of work.

Well, now I am being asked to run some statistics, and my boss wants
total time for created programming - meaning, he would like a sum of my
length field.

I have read some other postings saying to multiply my sum by 24.

Sum(length) * 24

However, I get a result of

370.699722222223

If I change the resulting format to hh:nn:ss, I get

16:47:36

Am I on the right track? Am I getting the correct result? HELP!?!

[salad]

si******@gmail.com wrote:

I have a video tape library that we use at work here in a MS-Access
database.

When originally desgined, we never thought ot convert the time on the
fly into total seconds, but instead, we stored all of the times in
hh:nn:ss format - because that is the way we thought about things
around here, and which would make sense in our line of work.

Well, now I am being asked to run some statistics, and my boss wants
total time for created programming - meaning, he would like a sum of my
length field.

I have read some other postings saying to multiply my sum by 24.

Sum(length) * 24

However, I get a result of

370.699722222223

If I change the resulting format to hh:nn:ss, I get

16:47:36

Am I on the right track? Am I getting the correct result? HELP!?!

Would the Second() function (see Help) work? This permits you to sum
the seconds.

[Bob Quintal]

si******@gmail.com wrote in
news:11**********************@m79g2000cwm.googlegr oups.com:

I have a video tape library that we use at work here in a
MS-Access database.

When originally desgined, we never thought ot convert the time
on the fly into total seconds, but instead, we stored all of
the times in hh:nn:ss format - because that is the way we
thought about things around here, and which would make sense
in our line of work.

Well, now I am being asked to run some statistics, and my boss
wants total time for created programming - meaning, he would
like a sum of my length field.

I have read some other postings saying to multiply my sum by
24.

Sum(length) * 24

However, I get a result of

370.699722222223

If I change the resulting format to hh:nn:ss, I get

16:47:36

Am I on the right track? Am I getting the correct result?
HELP!?!

I don't see any need for your output to be in days, so forget
the * 24..

About 2 years ago I created a pair of functions, sec2dur() and
dur2sec(), which convert the dd:hh:mm:ss format to seconds and
back again.

Feel free to modify it to suit your data.

Public function dur2sec( _
optional dd as integer = 0, _
optional hh as integer = 0, _
optional mm as integter= 0, _
optional ss as integer) as long

dur2sec = dd * 86400 + hh * 3600 + mm * 60 + ss

End Function


Public Function sec2dur(seconds As Long) As String
On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

sec2dur = Format(hrs, "#,##0") & ":" _
& Format(mins, "00") & ":" _
& Format(secs, "00")

End Function

I originally wrote them to do a simulated EDL(1) in Access, so
they should be just what you want. convert your formatted
duration with seconds: dur2sec([fieldname]) in a query, sum the
seconds, then convert back with sec2dur([seconds]) .

(1) Edit Decision List. a database of each clip to be used in
creating a video program.

--
Bob Quintal

PA is y I've altered my email address.

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

[The House of Miguel]

Thank you for your quick replies. However, I should have indicated that
not noly am I complete novice, but I am also performing all of these
queries right in Access itself.

All I need is a grand total of the "length field" in one of my tables,
and it can be in hh:nn:ss format, but I guess that format is incapable
of giving me a total of 435:45:04, because it cuts off at 24 hours -
from what I have read...

That last posting threw me for a loop. Is there something I can do that
is a littl emore straight forward. All I need is a total.

Thank you all for the help!

[Bob Quintal]

"The House of Miguel" <si******@gmail.comwrote in
news:11**********************@p79g2000cwp.googlegr oups.com:

Thank you for your quick replies. However, I should have
indicated that not noly am I complete novice, but I am also
performing all of these queries right in Access itself.

All I need is a grand total of the "length field" in one of my
tables, and it can be in hh:nn:ss format, but I guess that
format is incapable of giving me a total of 435:45:04, because
it cuts off at 24 hours - from what I have read...

That last posting threw me for a loop. Is there something I
can do that is a littl emore straight forward. All I need is a
total.

Thank you all for the help!

you cannot get much more straightforward that the code posted and
still get the results you want.
--
Bob Quintal

PA is y I've altered my email address.

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

[salad]

Bob Quintal wrote:

"The House of Miguel" <si******@gmail.comwrote in
news:11**********************@p79g2000cwp.googlegr oups.com:

>>Thank you for your quick replies. However, I should have
indicated that not noly am I complete novice, but I am also
performing all of these queries right in Access itself.

All I need is a grand total of the "length field" in one of my
tables, and it can be in hh:nn:ss format, but I guess that
format is incapable of giving me a total of 435:45:04, because
it cuts off at 24 hours - from what I have read...

That last posting threw me for a loop. Is there something I
can do that is a littl emore straight forward. All I need is a
total.

Thank you all for the help!

you cannot get much more straightforward that the code posted and
still get the results you want.

In a query, you can call a function like Bob's examples. If applying
within the query builder, enter something like
Expr1 : Sec2Dur(SecondsAmount)

Or you can use Dsum for a single value within some code
Dim strLen As String
Dim lngSecs As Long
'NZ used in case sum returns Null due to no finds
lngSecs = NZ(Dsum("Length","TableName","ID = 123"),0)
'convert the seconds value to a string
strLen = Sec2Dur(lngSecs)
...process further info

If you don't understand coding you might want to get a Microsoft
Step-By-Step book on the version of Access you are using.