TimeSerial

[KHogwood-Thompson]

Hi,
I have a table that stores a numeric value in the [TIME WRITTEN] field. Most
of the values are 6 numerics long i.e. 12:25:36 would store as 122536 want to
convert the values in that field to the format 00:00:00
I have used the following formula to do this:

TimeSerial(Left([TIME WRITTEN],2),Mid([TIME WRITTEN],3,2),Mid([TIME
WRITTEN],5,2))

This works fine on most of the values, however there are some values that
are 5 characters in length with the preceding zero not stored, i.e 08:56:34
would store as 85634. When the formatting is run on these values I get Errors.

Can anyone help with an adjustment to the formula to cater for these values??

 

[pietlinden]

Hi,
I have a table that stores a numeric value in the [TIME WRITTEN] field. Most
of the values are 6 numerics long i.e. 12:25:36 would store as 122536 want to
convert the values in that field to the format 00:00:00
I have used the following formula to do this:

TimeSerial(Left([TIME WRITTEN],2),Mid([TIME WRITTEN],3,2),Mid([TIME
WRITTEN],5,2))

This works fine on most of the values, however there are some values that
are 5 characters in length with the preceding zero not stored, i.e 08:56:34
would store as 85634. When the formatting is run on these values I get Errors.

Can anyone help with an adjustment to the formula to cater for these values??

Test for the length of the string first.

iif(len([Time Written])=6, TimeSerial(Left([TIME WRITTEN],2),Mid([TIME
WRITTEN],3,2),Mid([TIME WRITTEN],5,2)), TimeSerial(Left([TIME
WRITTEN],1),Mid([TIME WRITTEN],3,2),Mid([TIME WRITTEN],5,2)))

 

[KHogwood-Thompson]

Much more straightforward, thanks Rick!
--
K Hogwood-Thompson

Hi,
I have a table that stores a numeric value in the [TIME WRITTEN]
field. Most of the values are 6 numerics long i.e. 12:25:36 would
store as 122536 want to convert the values in that field to the
format 00:00:00
I have used the following formula to do this:

TimeSerial(Left([TIME WRITTEN],2),Mid([TIME WRITTEN],3,2),Mid([TIME
WRITTEN],5,2))

This works fine on most of the values, however there are some values
that
are 5 characters in length with the preceding zero not stored, i.e
08:56:34 would store as 85634. When the formatting is run on these
values I get Errors.

Can anyone help with an adjustment to the formula to cater for these
values??

Format(FieldName, "00:00:00")