# Division by Zero

I'm getting a Division by zero error with the following expression:
Attach Pct: Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100))

I have tried the IIF but can't seem to get it to work.
Microsoft Access

Last Comment
Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)

use the iif this way

iif([field1]=0,0,[Field2]/[Field1])
kateebebe

iif([LAYER IRO PERCENT]=0,0,Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))

Its not working for me.
<No points wanted>

Capricorns post basically says that you should do the "=0" for any/all fields that may be zero
mbizup

Depends on what you want to substitute for sero, but try this:

iif([Layer Attachment] = 0 OR [Layer IRO Percent] = 0, 0, Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))
Rey Obrero (Capricorn1)

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
mbizup

Got the fieldnames wrong in my earlier post above, but as pointed out by the others, you need to handle fields that can result in zeros in the denominator(s) :

iif([TIV] = 0 OR [Layer IRO Percent] = 0, 0, Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))
kateebebe

I get the error you tried to execute a query that does not include the specified expression as part of an aggregate function.
mbizup

>> you have to place the "avg" outside of the iif

Good point!  :)
kateebebe,

did you try the post at http:#a36917561 ?
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY