# Division by Zero Error

clk
Guest
Posts: n/a

 18th Jun 2012
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
Guest
Posts: n/a

 19th Jun 2012
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post ddiel Microsoft Access VBA Modules 10 30th Dec 2009 01:59 AM SylvieB Microsoft Access 5 22nd Sep 2009 06:55 PM =?Utf-8?B?Q3VwQ2FrZQ==?= Microsoft Word Document Management 3 15th Nov 2005 01:38 AM @(none) Computer Hardware 0 19th Aug 2004 11:57 PM Jeff Microsoft Access 2 8th Jan 2004 08:10 PM

Features