PC Review


Reply
Thread Tools Rate Thread

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.
 
Reply With Quote
 
 
 
 
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
division in access to round-up if division not whole number ddiel Microsoft Access VBA Modules 10 30th Dec 2009 01:59 AM
Show zero and not error when division by zero SylvieB Microsoft Access 5 22nd Sep 2009 06:55 PM
is there a division symbol. As in a maths division symbol =?Utf-8?B?Q3VwQ2FrZQ==?= Microsoft Word Document Management 3 15th Nov 2005 01:38 AM
Passmark Performance Test, Division, Floating Point Division, 2DShapes @(none) Computer Hardware 0 19th Aug 2004 11:57 PM
"division by zero" error message Jeff Microsoft Access 2 8th Jan 2004 08:10 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:26 AM.