Convert Text "00:00:00:00" to time format in Access 2007

  • Thread starter Thread starter Martin Prunty
  • Start date Start date
M

Martin Prunty

I have an application that uses a stopwatch to capture time on various
events. The stopwatch time is stored as a text file, which I would like to
convert to a time format. The text value is "00:00:00:00" which needs to be
converted to "dd:hh:nn:ss." I've tried just about every method I can find
with no luck thus far. There are a number of posts on this topic and I've
tried using "CDate," "DateSerial," "TimeSerial" and others, but each returns
compile errors when I attempt to run a query to do the conversion.

Any suggestions will be greatly appreciated. Thanks

Marty
 
I have an application that uses a stopwatch to capture time on various
events. The stopwatch time is stored as a text file, which I would like to
convert to a time format. The text value is "00:00:00:00" which needs to be
converted to "dd:hh:nn:ss."
Click to expand...

Did you write the original stop watch application? If so, you're going about
it all wrong. Create the field as a date field to start with and save the
time in it.

If you're taking the data from someone else's program and don't have the
luxury of getting the data in the proper format then do this:

dim Mydate as date
dim Stopwatch$
Mydate = val(Stopwatch$) + cdate(mid$(Stopwatch$,4))

The date will, of course, be stored relative to midnight of 12/30/1899, so
it'll look funny in the database, but it will work perfectly for addition and
subtraction purposes.
 
I have an application that uses a stopwatch to capture time on various
events. The stopwatch time is stored as a text file, which I would like to
convert to a time format. The text value is "00:00:00:00" which needs to be
converted to "dd:hh:nn:ss." I've tried just about every method I can find
with no luck thus far. There are a number of posts on this topic and I've
tried using "CDate," "DateSerial," "TimeSerial" and others, but each returns
compile errors when I attempt to run a query to do the conversion.

Any suggestions will be greatly appreciated. Thanks

Marty
Click to expand...

If this is a time duration, you're better off NOT storing it in a date/time
field, but rather in a Long Integer count of seconds. You can convert this
value to seconds with an expression

86400*Val(Left([stopwatch], 2)) + 3600*Val(Mid([stopwatch], 4, 2)) +
60*Val(Mid([stopwatch], 7, 2)) + Val(Right([stopwatch], 2))
 
Thanks for your note. As you might have figure out, I'm not a
programmer...just someone who has a need for an application.

Is there a trick to storing the data as a Long Integer Count of seconds?
Currently, I have the stopwatch record stored as text. Do I simply change
that field to a number field with Long Integer, or is there more to it?

Thanks again for your assistance.

Marty

John W. Vinson said:
I have an application that uses a stopwatch to capture time on various
events. The stopwatch time is stored as a text file, which I would like to
convert to a time format. The text value is "00:00:00:00" which needs to be
converted to "dd:hh:nn:ss." I've tried just about every method I can find
with no luck thus far. There are a number of posts on this topic and I've
tried using "CDate," "DateSerial," "TimeSerial" and others, but each returns
compile errors when I attempt to run a query to do the conversion.

Any suggestions will be greatly appreciated. Thanks

Marty
Click to expand...

If this is a time duration, you're better off NOT storing it in a date/time
field, but rather in a Long Integer count of seconds. You can convert this
value to seconds with an expression

86400*Val(Left([stopwatch], 2)) + 3600*Val(Mid([stopwatch], 4, 2)) +
60*Val(Mid([stopwatch], 7, 2)) + Val(Right([stopwatch], 2))
Click to expand...
 
Thanks for your note. As you might have figure out, I'm not a
programmer...just someone who has a need for an application.

Is there a trick to storing the data as a Long Integer Count of seconds?
Currently, I have the stopwatch record stored as text. Do I simply change
that field to a number field with Long Integer, or is there more to it?
Click to expand...

You would use the expression I posted to calculate the long integer (when you
need to treat the value as something other than a text string).

Do note that the text string as you post it will let you do quite a bit
already - it will sort chronologically, you can use an expression such as

< "01:12:00:00"

to find all durations less than a day and a half, etc.

It might help if you describe what you're trying to accomplish with this
value, and why you feel that you need to convert it to a date/time.
 
Martin Prunty said:
I have an application that uses a stopwatch to capture time on various
events. The stopwatch time is stored as a text file, which I would like
to
convert to a time format. The text value is "00:00:00:00" which needs to
be
converted to "dd:hh:nn:ss." I've tried just about every method I can find
with no luck thus far. There are a number of posts on this topic and
I've
tried using "CDate," "DateSerial," "TimeSerial" and others, but each
returns
compile errors when I attempt to run a query to do the conversion.

Any suggestions will be greatly appreciated. Thanks

Marty
Click to expand...
 
Back
Top