iif (Iserror ()) function still returning #error

function ms-access ms-access-2010
Nigel picture Nigel · Mar 26, 2014 · Viewed 40.5k times · Source

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

HansUp picture HansUp · Mar 26, 2014

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

Related questions

How to pass an array to a function in VBA?

I am trying to write a function that accepts an array as an argument. The array can have any number of elements. Function processArr(Arr() As Variant) As String Dim N As Variant dim finalStr as string For N = LBound(…

Read More
How do JavaScript closures work?

How would you explain JavaScript closures to someone with a knowledge of the concepts they consist of (for example functions, variables and the like), but does not understand closures themselves? I have seen the Scheme example given on Wikipedia, but …

Read More
Set a default parameter value for a JavaScript function

I would like a JavaScript function to have optional arguments which I set a default on, which get used if the value isn't defined (and ignored if the value is passed). In Ruby you can do it like this: def …

Read More