Calculation result is #error

  • Thread starter Thread starter mabi
  • Start date Start date
M

mabi

When inserting a calculation into a query, how would you
format the result to a default number (0) if the result
would be an error?

Example:
SELECT
.a,
.b, (
.a)/(
.b) AS c
ORDER BY (
.a)/(
.b) DESC

If a and b both contained 0, the result of (
.a)/
(
.b) would be "#ERROR" ... how can I get the field
to display "0"?

Thanks in advance for the help ....
 
create a function in a module, such as:

Function div0(AmountA, AmountB) As Double
If AmountA = 0 Or AmountB = 0 Then
div0 = 0
Else
div0 = AmountA / AmountB
End If
End Function

and use a query like:

SELECT Table1.Cost, Table1.Retail, div0([Table1].[a],[Table1].) AS
DivValue
FROM Table1
ORDER BY div0([Table1].[a],[Table1].);


Depending on what data types are in your data then use the appropriate data
types in the function, you could handle NULLs in the function too if you
wanted.
 
SELECT T.a, T.b, IIf([T.b = 0, 0, T.a / T.b) AS c
FROM YourTable As T
ORDER BY IIf([T.b = 0, 0, T.a / T.b) DESC
 
Back
Top