Converting HH:mm:ss to seconds

[neelsfer]

I have a timing program. My next challenge is to convert hh:mm:ss into seconds. A user of my program wants to calculate watts for a hill climb race.
the formula i am going to use is:

=athlete weight (kg) x 9.8 x elevation gain (meters) / time (seconds)

i have everything except the seconds. We use a special Garmin gps to calculate the elevation gain in meter and i get the weight of the athlete using a scale on the day of the race.
I get the results from my timing program in hh.mm.ss format.The field to convert to seconds is called "Racingtime "
I attach the file to show the time (hh:mm:ss in the query
Please help

[neelsfer]

I think it got it right. Converted it to minutes and then multiplied by 60 using this code in query

Timeseconds: TimeValue([RacingTime])*1440*60

[NeoPa]

Your question isn't very clear as to whether you have a string in "hh:mm:ss" format or a DateTime value in days. As attachments should only ever be supplied as extra clarification for a question (and should never be considered as being part of it - especially not instead of explaining the question properly), and because both answers may prove helpful generally, I will answer both. Please bear this in mind for future questions though ;-)

1. For a string "hh:mm:ss"
   This is best done with a public function in a standard module. Some of the tools to make this easier are not available for direct use in SQL.
   
   

   Public Function HMStoSec(strHMS As String) As Long
       HMStoSec = Split(strHMS, ":")(0) * 3600 + _
                  Split(strHMS, ":")(1) * 60 + _
                  Split(strHMS, ":")(2)
   End Function

2. For a DateTime value
   Essentially, your solution for a DateTime value would work. As there is no need to convert a time value using TimeValue() though, I would simplify it to :
   

   TimeSeconds: [RacingTime]*86400