If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Conversion Expression returns #Error
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. |
#2
|
|||
|
|||
Data Conversion Expression returns #Error
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)))) -- Build a little, test a little. "D. Stacy" wrote: 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. |
#3
|
|||
|
|||
Data Conversion Expression returns #Error
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 D. Stacy wrote: 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. |
Thread Tools | |
Display Modes | |
|
|