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