#Num! division with IIf statements

R

Robbie Doo

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.
 
J

Jacqueline

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

Klatuu

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.
 
R

Robbie Doo

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


Robbie Doo said:
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.
 
R

Robbie Doo

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.

Klatuu said:
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.
--
Dave Hargis, Microsoft Access MVP


Robbie Doo said:
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.
 
K

Klatuu

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


Robbie Doo said:
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.
 
R

Robbie Doo

Klatuu:

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

Klatuu said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top