Convert Time to text

W

Wylie C

I need to loop through my table and convert the field name Etime from a time
field formatted short time (hh:nn) to text. Can it be done and if so can I
get some help on the code..

Thanks again to all.
 
J

John Vinson

I need to loop through my table and convert the field name Etime from a time
field formatted short time (hh:nn) to text. Can it be done and if so can I
get some help on the code..

Thanks again to all.
Click to expand...

Could you explain? What output would you like to see? What use will be
made of this Text field once you convert it?

Note that a Date/Time datatype field is not stored with the formatted
characters; it's actually stored as a number, and can be displayed
with whatever format you like.


John W. Vinson[MVP]
 
W

Wylie C

John,

I have a cycling log in which I store miles rode and the elapsed time.
Originally I thought I'd only track the hours and minutes for the ET so I
used a date format of hh:nn, grabbed the hours value and the minutes value to
do the Average speed calculation. Since then I need to track the hh:nn:ss and
calculate the average speed. I have created a test db with a text field
formatted as 99:99:99 and then parsed the first two, second two and last two
values into variables and then calculate the ET. If I change the date field
to text I loose the values. If I can convert them somehow to a text field, I
can do the calculations. I could create another field and store the new value
there then delete the origianal field if necessary. The table name is
Statistics. I actually have two fields I need to do this in: Etime and Rtime.

Hope that is a clear enough explanation.
 
J

John Vinson

John,

I have a cycling log in which I store miles rode and the elapsed time.
Originally I thought I'd only track the hours and minutes for the ET so I
used a date format of hh:nn, grabbed the hours value and the minutes value to
do the Average speed calculation. Since then I need to track the hh:nn:ss and
calculate the average speed. I have created a test db with a text field
formatted as 99:99:99 and then parsed the first two, second two and last two
values into variables and then calculate the ET. If I change the date field
to text I loose the values. If I can convert them somehow to a text field, I
can do the calculations. I could create another field and store the new value
there then delete the origianal field if necessary. The table name is
Statistics. I actually have two fields I need to do this in: Etime and Rtime.
Click to expand...

Access Date/Time values are best used for storing points in time, not
durations - but I see no reason that you can't keep the data in either
a Date/Time or a numeric field. You're using it for arithmatic after
all - Text is the *least* desirable structure.

Note that the Format of a date/time field does NOT affect what's
stored. A Date/Time field is stored as a double float number, a count
of days and fractions of a day (times) since midnight, December 30,
1899; that is, a time value of 6:00 is actually stored as 0.25 and
corresponds to #12/30/1899 06:00:00#.

I would recommend storing your elapsed times in Long Integer seconds.
You can use three unbound textboxes txtHr, txtMin and txtSec with some
simple VBA code to convert to seconds:

Me!txtEtime = 3600*Me!txtHr + 60*Me!txtMin + Me!txtSec

This value can be summed, averaged, etc. etc. and displayed as
hours/minutes/seconds with an expression

ETime \ 3600 & Format(ETime \ 60 MOD 60, ":00") & Format(ETime MOD 60,
":00")

To move your date/time values into a new field, create the new field
and run an Update query updating the long integer field to

CLng(86400*CDbl([ETime]))

multiplying the number of seconds in a day by the fraction of a day in
your field.

John W. Vinson[MVP]
 
Top