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.
Attach Pct: Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100))
I have tried the IIF but can't seem to get it to work.
Last Comment
ASKER
iif([LAYER IRO PERCENT]=0,0,Avg(((([Layer
Its not working for me.
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
...so you have to adjust your syntax accordingly.
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.
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)))
iif([Layer Attachment] = 0 OR [Layer IRO Percent] = 0, 0, Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))
ASKER CERTIFIED SOLUTION
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.
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)))
iif([TIV] = 0 OR [Layer IRO Percent] = 0, 0, Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))
ASKER
I get the error you tried to execute a query that does not include the specified expression as part of an aggregate function.
>> you have to place the "avg" outside of the iif
Good point! :)
Good point! :)
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
use the iif this way
iif([field1]=0,0,[Field2]/