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

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

0
Utf
3/31/2010 8:20:10 PM
access 16762 articles. 3 followers. Follow

6 Replies
1014 Views

Similar Articles

[PageSpeed] 54

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

0
sfdavidkaye2
3/31/2010 11:18:54 PM
On Wed, 31 Mar 2010 13:20:10 -0700, Martin Prunty
<MartinPrunty@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]
0
John
4/1/2010 1:31:13 AM
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@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]
> .
> 
0
Utf
4/1/2010 2:33:02 AM
On Wed, 31 Mar 2010 19:33:02 -0700, Martin Prunty
<MartinPrunty@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]
0
John
4/1/2010 3:30:00 PM
"Martin Prunty" <MartinPrunty@discussions.microsoft.com> kirjoitti 
viestiss´┐Ż:EFD22292-F041-4C2B-AC6B-DCB6CAC73B1F@microsoft.com...
>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
> 


0
Risse
4/2/2010 6:51:52 AM
Thanks very much for your assistance.  It's now working great.

"Stephen Raftery" wrote:

> To reset your stopwatch when a new record is entered, just create a procedure 
> in the OnCurrent event:
> 
> sub Form_Current()
> me!stopwatch = 0
> end sub
> 
> 
> Stephen
> 
> 
> "Martin Prunty" wrote:
> 
> > Hi John,
> > 
> > Thanks for your assistance.  I was able to figure out how to use the formula 
> > you provided as an expression in a query that calculates the appropriate 
> > time.  It works great.  Can I ask one other question?
> > 
> > The application I'm preparing will allow me to essentially do "time and 
> > motion" studies using the stopwatch function.  I'm actually using the code 
> > provided on the Microsoft site as the means to create the stopwatch function. 
> >  (http://support.microsoft.com/kb/325238/en-us)   I'm then storing the 
> > elapsed time in a table as a text value.    I'm running into another problem 
> > that probably has a simple solution but has stumped me.  
> > 
> > After storing a new value in the table, I create a new record but when I 
> > restart the stopwatch to measure the new value, it begins with the elapsed 
> > time of the previous record.  Can you advise me how to change the code to 
> > restart the stopwatch to zero when a new record starts?
> > 
> > Many thanks.
> > 
> > Martin
> > 
> > 
> > 
> > 
> > 
> > "John W. Vinson" wrote:
> > 
> > > On Wed, 31 Mar 2010 19:33:02 -0700, Martin Prunty
> > > <MartinPrunty@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]
> > > .
> > > 
0
Utf
4/2/2010 7:39:01 PM
Reply:

Similar Artilces:

Convert Text "00:00:00:00" to time format in Access 2007
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 sug...