Error with Query based on IIF statements

[tylerpickering]

When I try to use this statement, I get an error--any idea as to what the issue may be?

Thanks!

Lane Type: IFF(AND(ISNUMBER(VALUE(MID( [Orig NCS] ,4,1))),ISNUMBER(VALUE(MID( [Dest NCS] ,4,1)))),"ZTZ",IFF(ISNUMBER(VALUE(MID( [Orig NCS] ,4,1))),"ZTP",IFF(ISNUMBER(VALUE(MID( [Dest NCS] ,4,1))),"PTZ","PTP")))


I copied it from excel and altered it as needed, not sure if there is something wrong with that...

[ranman256]

Access doesnt use AND that way. When 'IF' statements get this hairy...create a function. Much cleaner to read, and easily portable.
Inthe query, call it with: calcVal([Orig NCS])

Public Function calcVal(ByVal pvIntv)
Select Case Mid(pvIntv, 4, 1)
 Case "A"
    calcVal = "ZTZ"
 Case "B"
    calcVal = "ZTP"
 Case Else
    calcVal = "PTP"
End Select
End Function

[June7]

Access uses IIf(), Switch(), Choose() for conditional in-line expressions.

IsNumeric() instead of IsNumber().

Val() instead of Value().

Val() results in a number entity (however, it will error on Null) and a text value will return 0.

But you are extracting only 1 character from each field so don't see need for Val().

IIf(IsNumeric(Mid([Orig NCS],4,1)) AND IsNumeric(Mid([Dest NCS],4,1)),"ZTZ", IIf(IsNumeric(Mid([Orig NCS],4,1)),"ZTP", IIf(IsNumeric(Mid([Dest NCS],4,1)),"PTZ", "PTP")))