On Nov 8, 6:38 am, dwasb...@gmail.com wrote:
Hi Group (fairly limited knowledge of Access and almost none of Access
VBA. Using Access 2003).
I need to sum time, I've found through the groups archive an sql
extract that led me to this
SELECT Format(Sum(Table2.time),"Short Time") AS SumOftime
FROM Table2;
Which works fine but the article also said I would need a Function to
deal with sum when it exceeded 24.
I think I might of found it but could do with a direct explanation or
a little help with what I found
Function GetElapsedTime(interval)
Dim totalhours As Long, totalminutes As Long, totalseconds As
Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As
Long
days = Int(CSng(interval))
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60
GetElapsedTime = days & " Days " & hours & " Hours " &
Many thanks in anticipation
Don
The 'interval' consists of a nonnegative whole number representing
days followed by a decimal part representing the fraction of a day.
Think of 'interval' as a large barrel of liquid with marks on the
inside like those on the outside of a measuring glass for a day, an
hour, a minute and a second. The number in 'interval' corresponds to
how much liquid is in the barrel. Also assume that you have some
barrels labeled 'days' (a large barrel), 'totalhours', 'totalminutes'
and 'totalseconds'. You also have some measuring containers labeled
'day', 'hour', 'minute' and 'second'. You are to perform the
following procedure:
As long as you are not below the 'day' mark on the inside of the
'interval' barrel, measure out enough liquid to fill the measuring
container labeled 'day' and dump the liquid into the barrel labeled
'days'. Record how many times you had to do that. Now pour all the
liquid in 'days' back into 'interval'! Then repeat the process for
'totalhours', 'totalminutes' and 'totalseconds' using the
corresponding measuring marks and containers. Note: I prefer using a
method that doesn't force me to pour the contents back into the
original container. Since we're starting with days at the top (no
months), the tally for 'days' will be the number of days. The tally
for 'totalhours' is possibly too high because any hours included in
the tally for 'days' have been counted twice due to the liquid being
poured back into the original container. Taking the 'totalhours'
tally Mod 24 removes the hours that would be counted twice. The tally
for 'totalminutes' may also be too high by being counted in either the
'days' tally or in the 'totalhours' tally. Taking the tally for
'totalminutes' Mod 60 eliminates any minutes counted in the 'days'
tally along with any minutes counted in the 'totalhours' tally.
Similar reasoning applies to 'totalseconds'.
Methods that don't refill the initial barrel have more built-in
protection against an error of one second becoming an error of 59
seconds should a floating-point approximation somehow cause 'interval'
to produce one less 'totalminutes'. That should not actually happen
because 86400 is a relatively small integral multiple of 1440 causing
a total number of minutes barely under an integer to correspond to a
total number of seconds barely under an integer. I.e., if you get 59
'totalseconds' instead of 0 seconds due to an internal floating-point
approximation, you will also get one less 'totalminutes', keeping the
result within a second of the actual value.
In short, the function looks like it will work, assuming the last line
is fixed, to within a second provided 'interval' doesn't go beyond
about 24855 days (2147483647 {sec} / 86400 {sec/day}), i.e. about 68
years, but the technique is, IMO, slightly more clever than robust.
I'd almost say that the author got lucky concerning the floating-point
approximation case.
After writing the above I found the following note, after a Google
search, in:
http://groups.google.com/group/comp....22f0861bbe4d78
'----------------------------------------------
Function GetElapsedTime(dblInterval As Double) As String
'Returns a formatted string showing elapsed time
' dblInterval may be the difference between any two VBA dates.
' The sum or difference between two VBA dates returns an double
number,
' where the integer portion is the number of days and the fractional
' portion is the part of one day.
' Function returns correct elpased time up to two billion seconds or
' approximately 68 years. Causes runtime error for ages over 68
years.
'Source:
'INF: Functions for Calculating and Displaying Date/Time Values
'Article ID: Q88657
'Copyright (c) Microsoft Corporation. 1997.
'Variants of this function also appear in Microsoft's NeatCode.mdb
and
'in Getz's VBA Developer's Handbook as function FormatInterval.
Note that my solution for elapsed time in:
http://groups.google.com/group/micro...7e101c5176ebd8
is also limited to about 68 years, but the OP was talking about a
running contest :-). I note in passing that the choice of a Long data
type input conveniently obviated any floating-point approximation
considerations and allowed me to get away with a little bit of barrel
refilling. Maybe I got lucky also.
James A. Fortune
CD********@FortuneJames.com 
