Formula Not evaluating negative case of IIF statement correctly (1 Viewer)

[catbeasy]

The following formula returns -1 for all valid date values that are appended together from a string where the string field has 6 characters in it.

However, it will not return 0 for all values that do not meet this criteria. It shows nothing in the field..(when I put my cursor in one of these blanks, the cursor is right justified indicating a numerical value is supposed to be there?)

Expr2: IIf(Len(Trim([MBR02_CLOCK_NUM]))=6,IIf(IsDate(Left([MBR02_CLOCK_NUM],2) & "/" & Mid([MBR02_CLOCK_NUM],3,2) & "/" & "20" & Right([MBR02_CLOCK_NUM],2)),-1,0))

Any help on why these "invalid" records are not evaluating to a 0 is appreciated..

Thanks..

 

[catbeasy]

Never mind, this is a logical error regarding the data, I see what it is doing now..thanks..

 

[boblarson]

I believe because you've only given the second IIF a value if false but you didn't give the first one a false value:

Expr2: IIf(Len(Trim([MBR02_CLOCK_NUM]))=6,IIf(IsDate(Left([MBR02_CLOCK_NUM],2) & "/" & Mid([MBR02_CLOCK_NUM],3,2) & "/" & "20" & Right([MBR02_CLOCK_NUM],2)),-1,0),0)