Calculation result is #error

[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 ....

 

[Sean]

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.

 

[Van T. Dinh]

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