IIF Formula Error

[phanikumarbs]

While Executing a Query which has below formula i am getting Over flow error

Format(Val(IIf(InStr([Shipper'sReferenceNumber]," ")>0,Left([Shipper'sReferenceNumber],InStr([Shipper'sReferenceNumber]," ")-1),[Shipper'sReferenceNumber])),"0")



Kindly Help me

Thanks and Regards
Phani Kumar

[June7]

Problem with IIf is that every part must be able to evaluate, even if its not the part that returns value.

So if there is no space in the string, the Left will fail because can't have negative length, therefore the entire IIf expression fails.

So maybe make sure there is a space.

Format(Val(Left([Shipper'sReferenceNumber] & " ", InStr([Shipper'sReferenceNumber] & " ", " ") - 1)), "0")

If this doesn't resolve issue, provide sample of data in the field.


BTW, recommend no spaces or special characters/punctuation (underscore is exception) in name convention.

[phanikumarbs]

Still Getting the Overflow error

[June7]

My testing with the concatenated space works so something else is wrong. Actually, the negative length would cause a different error message - "Invalid procedure".

If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved.