Solved

Access query syntax - division by zero in aggregate function

Posted on 2014-02-25
3
817 Views
Last Modified: 2014-02-25
Dear experts -
hopefully an easy one.
I am calculate an average using the formula below - which works well, however i want to avoid division by zero, which is giving me a #Num! error for certain results.

Any suggestions?
Thanks!

TR_Reg: Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,[e].[billrate],0))/Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,1,0))

Open in new window

0
Comment
Question by:terpsichore
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39885125
Hi,

Maybe

TR_Reg: Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,[e].[billrate],0))/IIF(Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,1,0))=0,null,Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,1,0)))

Open in new window

Regards
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39885126
This is one method, to first check if the sum is zero:

TR_Reg:
IIf(Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,1,0))=0, 0,
Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,[e].[billrate],0))/Sum(IIf([e].[billunit]=1 And [e].[volumeunit]=1 And [accounts].[regulatory]=True And [e].[action_ID]=38,1,0)))

/gustav
0
 

Author Closing Comment

by:terpsichore
ID: 39885144
This worked - thank you.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question