Text to Time converstion problem

[D. Stacy]

The goal of this effort is to convert text data into time data. The data
comes in 0045, 0200, 0530, etc. BUT it does not always come in in 4 digit
text length strings.

The below expression works fine when the data is length = 4 but it produces
errors when it starts with a zero length string (fields that look blank but
in fact are one space).

CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])=0,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",FormatDateTime(IIf(Len([Cut_Time])=0,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)))


Review: Input 0050 or 1400 or 545 or " "

Ouput 12:50 AM or 2:00 PM or 5:45 PM or 12:00 AM

 

[Jerry Whittle]

The ASCII value for a space is 32 while the values for 0 - 9 are 48 - 57.

You could use the ASC function to check that the ASCII value of the first
character is between 48 to 57 then handle the problem from there.

ASC([Cut_Time]) Between 48 and 57 or just ASC([Cut_Time]) > 47 could work.

The ASC function only evaluates the first character of a string so there's
no need to do a Left or anything. The ASC function will return an error on
Nulls or ZLSs.

BTW: a space and a zero length string are NOT the same. A space is " " while
a ZLS is "".

 

[John Spencer]

Try the following expression:

IIF(IsDate(Format(Cut_Time + "00","00:00:00")),CDate(Format(Cut_Time +
"00","00:00:00")),Null)

You can then apply a format to that time if you want to
Format(IIF(IsDate(Format(Cut_Time + "00","00:00:00")),CDate(Format(Cut_Time +
"00","00:00:00")),Null),"hh:nn AM/PM")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County