Prev: why is it saying program has expired when i just downloaded
Next: Automotive database template.
From: Martin Prunty on 31 Mar 2010 16:20
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

From: David Kaye on 31 Mar 2010 20:18
<MartinPrunty(a)discussions.microsoft.com> wrote:

>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."

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.

From: John W. Vinson on 31 Mar 2010 21:31
On Wed, 31 Mar 2010 13:20:10 -0700, Martin Prunty
<MartinPrunty(a)discussions.microsoft.com> wrote:

>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

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))

--

John W. Vinson [MVP]
From: Martin Prunty on 31 Mar 2010 22:33
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" wrote:

> On Wed, 31 Mar 2010 13:20:10 -0700, Martin Prunty
> <MartinPrunty(a)discussions.microsoft.com> wrote:
>
> >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
>
> 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))
>
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 1 Apr 2010 11:30
On Wed, 31 Mar 2010 19:33:02 -0700, Martin Prunty
<MartinPrunty(a)discussions.microsoft.com> wrote:

>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?

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.
--

John W. Vinson [MVP]
 |  Next  |  Last
Pages: 1 2
Prev: why is it saying program has expired when i just downloaded
Next: Automotive database template.