By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

Convert Elapsed Time to Seconds

P: n/a
I need to be able to enter an elapsed time into a text
field. This time then needs to be converted into seconds
and stored in a field in some table.

The user will enter a time (format is h:minutes:seconds)
as such:

0:15:34 (0 hours, 15 minutes, 34 seconds -> 934 seconds total)

or as another example:

1:02:10 (1 hour, 2 minutes, 10 seconds -> 3730 seconds total)

Can someone please walk me through this? I imagine this is
VBA related (an Event Procedure), but I'm not sure of the
code or what 'Event' type to out this under for the given
text field.

Thanks.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
lucky for you, Access has 3 functions to assist your needs, and they
can be used in queries or in VB under forms to calculate a control to
a value that will update the desired table field.

These fuctions are hour(), minute(), and second(). Each one takes a
date/time OR a string time format, i.e. (like your user's will enter):
"03:45:23".

Then, all you need to do is run these three functions on that field to
achieve your result: hour(<dateORtextField>) * 3600 +
minute(<sameField>) * 60 + second(<sameField>).

That's it.
-Paul T.

fr*******@yahoo.com (fred14214) wrote in message news:<90**************************@posting.google. com>...
I need to be able to enter an elapsed time into a text
field. This time then needs to be converted into seconds
and stored in a field in some table.

The user will enter a time (format is h:minutes:seconds)
as such:

0:15:34 (0 hours, 15 minutes, 34 seconds -> 934 seconds total)

or as another example:

1:02:10 (1 hour, 2 minutes, 10 seconds -> 3730 seconds total)

Can someone please walk me through this? I imagine this is
VBA related (an Event Procedure), but I'm not sure of the
code or what 'Event' type to out this under for the given
text field.

Thanks.

Nov 12 '05 #2

P: n/a
SELECT tblTimes.ID, tblTimes.TextTime, CDate([TextTime]) AS TestTime,
DateDiff("s",Int(CDate([TextTime])),CDate([TextTime])) AS TestDiff
FROM tblTimes;

Create a numeric field in your table and set its type to long.

Then create an update query to fix all the values for you.

UPDATE MyTable
SET MyElapsedTimeField =
DateDiff("s",Int(CDate([TextTime])),CDate([TextTime]));

BTW, storing derived values usually isn't a good idea. I would base a
query on another query until I got what I wanted... much more
flexible.
Nov 12 '05 #3

P: n/a
Thank you for your time. I have another question.

I am basing a new query on an existing query. In the existing query is a
field of seconds. In the new query, I would like to get the average of
the 'field of seconds' from the old query. I can accomplish this by
using the Group By -> Avg function. This works, but it gives the answers
in seconds. How can I get the new query to display (in the query itself,
not a form) hours:minutes:seconds?

Thank you again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.