iif (Iserror ()) function still returning #error

Question

I have the following function that creates a column in my query:

MTD: IIf(IsError(FormatNumber([62xx]![F40])),0,FormatNumber([62xx]![F40]))

This is linked to an Excel file and where people put numbers and text in the same column (F40 in this example). I need to know if the thing I am looking at is a number or text. If it's text I want a zero, if it is a number I want the number. I know that when I use FormatNumber([C107_62xx]![F40]) on a text line I get an error. I would assume when I get an error, then my iif formula above would convert that to a zero and the world would rejoice. For some reason I am still getting a #error even with my iif statement. What am I doing wrong?

I have also tried using the IsNumeric function but I still get #NUM! errors that come through.

Answer 1

IsError does not do what you think it does. From the help topic, it "Returns a Boolean value indicating whether an expression is an error value." Not whether the expression triggers an error, but whether the expression is an error value.

Sorry, that explanation was probably not clear enough, but I don't know how to do better. So I'll just suggest you consider this IsNumeric() expression for what you want here.

IIf(IsNumeric([62xx]![F40]), FormatNumber([62xx]![F40]), 0)

Here is that same expression in a query with the output below.

SELECT
    [62xx].F40,
    IIf(IsNumeric([62xx]![F40]), FormatNumber([62xx]![F40]), 0) AS MTD
FROM [62xx];

F40    MTD
-----  ----
foo    0
1      1.00
2.345  2.35
bar    0