Data Conversion Expression returns #Error

[D. Stacy]

Objective: Convert txt data representing a time stamp on a particular
activity into DateTime format.
1. If the data field contains any data convert it to the DateTime format
2. If data field is Null or a zero length string would like to return a null.
3. The data usually come in 0000 format but it could be 000 or 00 or 0 or “ “.

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

The expression works fine if the original txt field contains normal
(expected) data but returns an #Error if field starts with a zero length
string or a null.

 

[KARL DEWEY]

Try this --
CutT_temp: IIF([Cut_Time] Is Null OR [Cut_Time] ="", Null
CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0))))

 

[John Spencer]

So, what time is 23? 23:00 or 00:23.

I proposed a solution to this in another post of yours on the 19th. Did you
try the following and did it work or not? If it failed, can you tell us the
problem with the solution. The only thing I can see it that if CutTime was a
zero-length string (zls) then you would get a time of midnight. That can be
handled by testing for that possibility in the IIF statement.

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")

To test for null or zls or multiple spaces you could use

IIF(Len(Trim(Cut_Time & ""))>0 AND IsDate(Format(Cut_Time &
"00","00:00:00")),CDate(Format(Cut_Time & "00","00:00:00")),Null)


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