Actually, as written, you can very possibly get a divide by zero.
Make the following changes and you will not get the #num and will not get a
divide by zero error.
=Sum(IIf([Findings]<>1 And
Nz([Month],0)=DateAdd("m",-4,Forms.ReportCriteria5.txtEndDate),Nz([ErrorAmnt],0),0))/Sum(IIf(Nz([Month],0)=DateAdd("m",-4,Forms.ReportCriteria5.txtEndDate),Nz([Allot],0),1))
As I stated in my previous post, in your divisor logic you use [Allot] and
it is 0, you will get a divide by zero error by specifyin 0 and the False
argument of the IIf function. That would end up as some number / 0
You can't do that. If you change that to 1 as I have in the example above,
it will divide the other number by 1. If that is not what you want it to do,
then use something else.
Also, Month should not be used as a name. It is an Access reserved word and
can confuse Access as to whether you are refering to a name or the Month
function.
--
Dave Hargis, Microsoft Access MVP
Robbie Doo said:
Jacqueline:
In reality it's not divided by zero, these are nulls divided by nulls. No
datas are in the fields. The problem is when the entire fields are empty in a
particular date-range that's where I get the #Num!
Jacqueline said:
Robbi,
Your problem is you cannot ever divid by zero, usally the error you are
getting is an indication that you are attempting a divid by zero. You will
have to work out something that takes care of your nulls and is there a
chance that you could have blank cells as well?
Blanks are treated a little differently than nulls, a cell that had
something in it, that may have been removed or a space??
--
Jacqueline
:
I divide 2 IIf statements. They work fine as long as no null is present.
Here are my statements:
=Sum(IIf([Findings]<>1 And
[Month]=DateAdd("m",-4,Forms.ReportCriteria5.txtEndDate),[ErrorAmnt],0))/Sum(IIf([Month]=DateAdd("m",-4,Forms.ReportCriteria5.txtEndDate),[Allot],0))
Do I need to insert anything in here to prevent #Num! from appearing? I
would prever 0% instead. BTW, this statement puts out a Percentage.
Thank you for any help.