Calculation returns #Error

[newtovba]

Hi,

I have a query which returns colums based on calculations. One of those calculations can sometimes result in a return value of #Error (whenI translate it I get Error Number 5).

My first question is why I get the error in the first place. The calcualtion works in Excel. Try:

Dim a as Double
a = (500/-292)^(1/5)

If you type this calculation into Excel it returns a value. Access returns an Error. I would love to know if there is something I can do in Access to get the correct result back.

If however I cannot do this, then I would like to trap the error and display/output a blank field rather than the error. I have tried using the IsError function but this does not see the error.

HELP!!!!!!

 

[Mile-O]

If however I cannot do this, then I would like to trap the error

Reading about the ^ operator in the help files, you'd have got this:

^ Operator

Used to raise a number to the power of an exponent.

Syntax

result = number ^ exponent

The ^ operator syntax has these parts:

Part Description
result Required; any numeric variable.
number Required; any numeric expression.
exponent Required; any numeric expression.

Remarks

A number can be negative only if exponent is an integer value. When more than one exponentiation is performed in a single expression, the ^ operator is evaluated as it is encountered from left to right.

500/-292 (the number) returns a negative value. 1/5 (the exponent) returns 0.2 which is not an integer value. This means you will receive an error.

So, try:

Dim a as Double
a = Abs(((500/-292))^(1/5))*-1

 

[newtovba]

Okay. Makes sense. It doesn't explain though,why Excel will let you do it. Also, the data is coming from a query which is retrieving multiple records. the majority of results will be positive, but I have no way of knowing before hand if any of the components of the calculation are negative.

Thank you for your response.

 

[Mile-O]

I have no way of knowing before hand if any of the components of the calculation are negative.

Post your SQL and I'll write you a function.