IsError Function

[jalbao]

I am confused on how the IsError function works (or doesn't work for that matter).

I have a SELECT statement that includes an expression:

SELECT (A.foo/B.bar) AS Expr1
FROM A INNER JOIN B...

Notice that if B.bar is a "zero" value, division by zero occurs. So I implemented the IIF and IsError functions thinking that the IsError will return a True / False value if/when an error occurs. Then the IIF would return "safe" values based on the IsError's returned value.

Here's an example of the function implementation.

SELECT IIF(IsError((A.foo/B.bar)),"bad","good") AS Expr1
FROM A INNER JOIN B...

When I run the query with the functions, I get a return of "#Error" if the divisor (B.bar) equals zero. Based on how I wrote the query, I would think that I should see a return of "bad" instead of "#Error". For all results that did not have a zero value in the divisor, I am getting a result of "good" back - which is what I expect.

Can anyone fill me in on why my SELECT statment does not return "bad" when an error occurs in the expression?

Please let me know if I you need further explanation - I would really enjoy hashing this one out with anyone interested.

 

[PHV]

Why not simply add ' AND B.bar<>0 ' in the WHERE clause ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jalbao]

In a nutshell, the SELECT statement is a user defined expression. Meaning that I don't know which field(s) is a divisor. The fact that the user is able to create his/her own expression imply's that any expression is possible. Instead of making my own parser that will figure out what's going on with the expression, I thought it much easier to just trap for any error on the expression - I just mentioned "divide by zero" as an example.

 

[dhookom]

I would use:

SELECT IIf(B.bar=0,0,A.foo/B.bar) AS Expr1
FROM A INNER JOIN B...


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[jalbao]

So I take it that no one knows how the IsError function works?

 

[MoLaker]

As I understand it, IsError is a VB.net function, not an Access function. I can't help either, but you might post the question on the VB.net forum.

 

[PHV]

Have you even tried the F1 key ?
IsError(expr) returns True when expr can be evaluated as an error number, ie a variant with VarType = vbError (10).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MoLaker]

Sorry, I just noted it is also an Excel function. But, I don't see how it could be applied in a Select statement from MS Access or JET.

 

[jalbao]

If I run the following query in Access:

SELECT IIF(ISERROR(4),"ERROR","NO ERROR")

I get a result set back of "NO ERROR"

SELECT IIF(ISERROR(),"ERROR","NO ERROR")

*Note that I removed the argument from the IsError() function.

I get the error message: "Wrong number of arguments used with function in query expression..."

I would assume then, that if IsError() is not a valid Access function, I wouldn't have received either of the results that I just mentioned.

For example - I just ran the following query:

SELECT IIF(IsFakeFunction(),"ERROR","NO ERROR")

I got an error message stating: "Undefined function 'IsFakeFunction' in expression."

Hmm.. I'm confused on this one :|

 

[PHV]

Try this:
SELECT IIF(ISERROR(CVerr(4)),"ERROR","NO ERROR")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jalbao]

Returns error message: "Undefined function 'CVerr' in expression.

 

[jalbao]

Same context, different topic...

What is the extent of error handling functions available to us? It would seem to me that there has to be some way to handle errors at runtime in an sql statement... don't you think?