time format

[eb1mom]

I am having a problem correctly using a time formating
function. I have function in module and can use it in
query. It works well. I want to use function to format sum
in the footer of a form. I have a text box =SUM([seconds]).
This text box displays total seconds. I want to display the
result using TimeSpentS function. I have written
---TimeSpentS=SUM([seconds])--- many different ways, with
only an error message as result. How should I correctly
call this function?


Function TimeSpentS(ByVal pSec As Single) As String
'*******************************************
'Purpose: Formats an integer (number of
' seconds) into a string

'*******************************************
'
Dim days, hours, minutes, secs, timehold As Double
Dim fmt As String

fmt = "00:"

timehold = pSec

days = Int(timehold / 86400)

timehold = timehold - (days * 86400)

hours = Int(timehold / 3600)

timehold = timehold - (hours * 3600)

minutes = Int(timehold / 60)

secs = timehold Mod 60
'
TimeSpentS = Format(LTrim(Str(days)), fmt) &
Format(LTrim(Str(hours)), fmt) _
& Format(LTrim(Str(minutes)), fmt) &
Format(LTrim(Str(secs)), "00")

End Function

 

[John Vinson]

Function TimeSpentS(ByVal pSec As Single) As String

Since the function returns a String, a Sum won't work: you can add
numbers but you can't add Strings! (What's 3 plus QRS?)

You'll need to either modify the TimeSpentS function to return a Long
or use the Val() function to convert the string to a number.