# Division by Zero Error

Discussion in 'Microsoft Access Queries' started by clk, Jun 18, 2012.

1. ### clkGuest

I am trying to make a summary query to show the percent of change in
test scores. I have it working except when a zero is entered.

In my query I have the following two fields:

PercentMoney: IIf([LegalCorrect]=0,0,[LegalCorrect]/[Legal])

PctGain: IIf(IsNull([PrevDate]) Or NZ([PercentMoney]=0),0,
(([PercentMoney]/
NZ(DLookUp("PercentMoney","qryDanielMemorialScoresLegalSummary","DMDate=
#" & [PrevDate] & "#"),0)))-1)*100

I thought I was handling the division by zero errors but it is still
throwing that error message.

Any help would be greatly appreciated.

Thank you.

clk, Jun 18, 2012

2. ### Rob ParkerGuest

To use an IIf function to prevent a division by zero error, you need to test
the term which is the divisor, not the dividend. So, for example, your
first expression needs to be something like:

PercentMoney: IIf([Legal]=0,0,[LegalCorrect]/[Legal])

or maybe, depending on exactly what you're trying to achieve:
PercentMoney: IIf([Legal]=0,[LegalCorrect],[LegalCorrect]/[Legal])
or maybe something else - I don't understand exactly what you're trying to
accomplish here.

HTH,

Rob

BTW: the term NZ([PercentMoney]=0) in your expression for PctGain seems
strange; it will return 0 for every value of PercentMoney except Null, and
will return Null if PercentMoney is Null. Perhaps you want
nz([PercentMoney],0), which will return the actual value of PercentMoney
except when it is null, in which case it will return 0.

"clk" <> wrote in message
news:...
>I am trying to make a summary query to show the percent of change in
> test scores. I have it working except when a zero is entered.
>
> In my query I have the following two fields:
>
> PercentMoney: IIf([LegalCorrect]=0,0,[LegalCorrect]/[Legal])
>
> PctGain: IIf(IsNull([PrevDate]) Or NZ([PercentMoney]=0),0,
> (([PercentMoney]/
> NZ(DLookUp("PercentMoney","qryDanielMemorialScoresLegalSummary","DMDate=
> #" & [PrevDate] & "#"),0)))-1)*100
>
> I thought I was handling the division by zero errors but it is still
> throwing that error message.
>
> Any help would be greatly appreciated.
>
> Thank you.

Rob Parker, Jun 19, 2012