Modify Time Field

[Joe]

I have a field that contains time in the 24 hr. format
(and it is a number (long integer) type field.

I want to change it to a TIME field.

The problem is that the field doesn't contain leading
zeros. The formula that I'm using is:

TimeSerial(Left(CStr([test].[AHTime]),2),Mid(CStr([test].
[AHTime]),3,2),Right(CStr([test].[AHTime]),2))

The formula works properly only on fields that contain
data like 114523. I get 11:45:23 AM, which is correct.
Except that if the field is 51301 I get 1/1/1900 3:30:01
AM instead of 05:13:01 AM. Same goes for if I only have a
value of 23, which would be 00:00:23 AM.

How can I get this to work for all possibilities? May be
there is a way to add leading zeros so that the field is
always 6 digits, as it should be? Or perhaps some other
way? Any ideas?

 

[John Spencer (MVP)]

Use the Format function to force six numbers; in that case you don't need the
Cstr function

Format(AHTime,"000000")

That will force the number into a string of six characters.
So 51301 will be 051301.

TimeSerial(Left(Format([AhTime],"000000")),2), ...

Another option would be to use the TimeValue function

TimeValue(Format(53101,"00:00:00")) returns 5:31:01 AM

I would probably add a test first to make sure that AhTime was not null,

You can also add in additional testing with the isdate function to handle other
errors such as
555555
which will return an error.

So you might try the following NOT FULLY TESTED formula

IIF(IsDate(Format(Nz(AhTime,"A"),"00:00:00")),
TimeValue(Format(AhTime,"00:00:00")),
Null)

The test IsDate(Format(Nz(12,"A"),"00:00:00"))
should catch fields where AhTime is
Null
Too large in any segment
Negative