#Num! division with IIf statements

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robbie Doo - 27 Mar 2008 15:42 GMT

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.

Reply to this Message

Jacqueline - 27 Mar 2008 18:28 GMT

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??

Signature

Jacqueline

> I divide 2 IIf statements. They work fine as long as no null is present.
> Here are my statements:
[quoted text clipped - 6 lines]
>
> Thank you for any help.

Reply to this Message

Robbie Doo - 27 Mar 2008 23:03 GMT

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!

> Robbi,
> Your problem is you cannot ever divid by zero, usally the error you are
[quoted text clipped - 15 lines]
> >
> > Thank you for any help.

Reply to this Message

Klatuu - 27 Mar 2008 23:12 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> Jacqueline:
>
[quoted text clipped - 21 lines]
> > >
> > > Thank you for any help.

Reply to this Message

Robbie Doo - 27 Mar 2008 23:36 GMT

Klatuu:

Looks like that corrected the problem. I get 0% instead of the error msg.
Thank you.

> Actually, as written, you can very possibly get a divide by zero.
>
[quoted text clipped - 40 lines]
> > > >
> > > > Thank you for any help.

Reply to this Message

Klatuu - 27 Mar 2008 19:30 GMT

IIf([Month]=DateAdd("m",-4,Forms.ReportCriteria5.txtEndDate),[Allot],0))
Your
problem is here --^

You can't divide by zero. It is a mathmatical impossibility. Change it to
a 1.

Signature

Dave Hargis, Microsoft Access MVP

> I divide 2 IIf statements. They work fine as long as no null is present.
> Here are my statements:
[quoted text clipped - 6 lines]
>
> Thank you for any help.

Reply to this Message

Robbie Doo - 27 Mar 2008 23:06 GMT

Klatuu:

I tried the 1 but it doubles up the result. It's divided by null, but when I
remove the zeros from the statement I get blanks, which are ok. However, I
would like to see zeros instead of blanks.

> IIf([Month]=DateAdd("m",-4,Forms.ReportCriteria5.txtEndDate),[Allot],0))
> Your
[quoted text clipped - 13 lines]
> >
> > Thank you for any help.

Reply to this Message