Convert Elapsed Time to Seconds

[fred14214]

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:secon ds)
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.

[PaulT]

Re: Convert Elapsed Time to Seconds

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(<dateORtex tField>) * 3600 +
minute(<sameFie ld>) * 60 + second(<sameFie ld>).

That's it.
-Paul T.

fred14214@yahoo .com (fred14214) wrote in message news:<901cc71a. 0309061212.64ff 0016@posting.go ogle.com>...[color=blue]
> 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:secon ds)
> 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.[/color]

[Pieter Linden]

Re: Convert Elapsed Time to Seconds

SELECT tblTimes.ID, tblTimes.TextTi me, CDate([TextTime]) AS TestTime,
DateDiff("s",In t(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 MyElapsedTimeFi eld =
DateDiff("s",In t(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.

[Fred Hartnett]

Re: Convert Elapsed Time to Seconds

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:s econds?

Thank you again.



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