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.  
kateebebeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

use the iif this way

iif([field1]=0,0,[Field2]/[Field1])
kateebebeAuthor Commented:
iif([LAYER IRO PERCENT]=0,0,Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))

Its not working for me.  
Jeffrey CoachmanMIS LiasonCommented:
<No points wanted>

Capricorns post basically says that you should do the "=0" for any/all fields that may be zero
...so you have to adjust your syntax accordingly.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbizupCommented:
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)Commented:
you have to place the "avg" outside of the iif


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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
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)))
kateebebeAuthor Commented:
I get the error you tried to execute a query that does not include the specified expression as part of an aggregate function.
mbizupCommented:
>> you have to place the "avg" outside of the iif

Good point!  :)
Rey Obrero (Capricorn1)Commented:
kateebebe,

did you try the post at http:#a36917561 ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.