SUM(TIME FIELD)

[Fred]

Hi,
I'm making a query that make the sum of a date/time field.
The field works.
BUT
when in my query I make the :
SELECT SUM (myfield) FROM .........

The results show my some decimal numbers...
WHY ?
How can I format this to have an TIME stamp ?

Thank you

 

[Michel Walsh]

Hi,


The sum( ) returns the number of days (and decimal portion of a day,
0.25 = 1/4 of a day = 6 hours , as example).

If the sum is less than one, you can

format( sum( ... ), "hh:nn:ss" )

If the sum is larger than one, there is no predefined format allowing
more than 24 hours ( kind of "hhh" ), so, you have to generate them
yourself:


format( Int(24*sum( ...)), "0") & format( sum(...) , ":nn:ss" )




Hoping it may help,
Vanderghast, Access MVP

 

[Fred]

And I can add this in my SQL query ?
wow !

Hi,


The sum( ) returns the number of days (and decimal portion of a day,
0.25 = 1/4 of a day = 6 hours , as example).

If the sum is less than one, you can

format( sum( ... ), "hh:nn:ss" )

If the sum is larger than one, there is no predefined format allowing
more than 24 hours ( kind of "hhh" ), so, you have to generate them
yourself:


format( Int(24*sum( ...)), "0") & format( sum(...) , ":nn:ss" )




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


You can do it in the query, or outside it, in that last case, making the
computation each time you need to display it. If some computation is done
inside the query, THAT computed expression won't be updateable, is it
important to mention it? If you use JET+Access, any VBA function, even those
you would write in a standard module, can be use inside an SQL statement.



Vanderghast, Access MVP