#Num! division with IIf statements

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

 

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

 

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

 

[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!

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.

 

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

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

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.

 

[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

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

 

[Robbie Doo]

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.

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

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