#error / division by zero

[Banna]

The long and the short of it, I know enough about Access to be dangerous but not enough to be effective.

I have a query which I have written an expression as follows:
expr1:100-([sumofquantity]/[built]*100). "sumofquantity is a query and built is a table. In many cases when there are no units built, the entry is a zero. With this formula,the division by zero results in "#error" message. I tried to write an "if" statement in criteria but it results in a "overflow" error.
=if([built=0,"","sumofquantity/built")
How do I write the expression so there is no "#error"and the field is either left blank or defaults to zero.



I appreciate everyone's patience with the not so skilled.

Brian

 

[Jon K]

Expr1: IIf([built]=0, Null, 100-([sumofquantity]/[built]*100))

 

[Banna]

Thank You

Jon,
Thank you, a million times over. Perfect!!!

 

[chubbardsr]

I've always hated this problem myself. I know the posting is an old one, but it's still nice to have something that worked updated.... I created my function since i won't always know which one of the numbers will be 0, null, empty, etc....

What i've done to work around this is....


Function DivideZeros(l As Variant, ll As Variant) As Variant
On Error GoTo Error

If IsNull(l) Or IsNull(ll) Then GoTo Error

DivideZeros = l / ll

Exit Function
Error:
DivideZeros = 0
End Function

Then from here you can call the function from a query, form, vba, etc
MyVar = DivideZeros(Var1, Var2)
MyVar = DivideZeros(1, 100)
MyVar = DivideZeros(0, 100)

Enjoy

 

[Chelle1661]

Chubbardsr,
As a very amateur access user, [I have no VBA or SQL] please forgive my question:
Where would I place this function?
I continually waste time on these zero problems so would really like to try your solution.
I did try to create my calc field in a report but have been troubleshooting by inserting the calc field in the underlying query instead.
My IIf statements [no matter who's advice I take] keep coming up with an invalid syntax error!
My current problem:
http://www.access-programmers.co.uk/forums/showthread.php?p=1308632&posted=1#post1308632

Hoping you can help!