7

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Nigel
  • 309
  • 1
  • 4
  • 16

1 Answers1

8

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
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • I don't understand why IsError doesn't do what I think it does, but I did try your suggestion. IsNumeric isn't being very reliable for some reason. I don't like pulling data from spreadsheets where peple do crazy stuff. This seems to work though: `IIf(IsNumeric(FormatNumber([62xx]![F40])),FormatNumber([62xx]![F40]),0)` – Nigel Mar 26 '14 at 19:36
  • Sometimes Excel and Access don't play as nice as I want them to. Thank you @HansUp for your help. – Nigel Mar 26 '14 at 19:45
  • Yeah, about that first part ... `IsError()` can't be used to *trap* an error, only tell you whether something is an error value. For example, dividing by zero triggers error #11, "Division by zero". But `IsError(1/0)` will not say True; instead it triggers error #11. And `IsError(11)` says False. An actual error value is a different type of critter. And `IsError(CVErr(11))` does return True. – HansUp Mar 26 '14 at 19:50
  • So @HansUp how do you get Acces to tell you if something is an Error? – Jonathan Elkins Sep 10 '19 at 05:34
  • @JonathanElkins Best I can offer is to set up error handling with `On Error GoTo ...` and then check `Err.Number` and `Err.Description` – HansUp Sep 10 '19 at 06:12