Divide by Zero Error 2

[Snookless]

Hello All..
I am using an "if then" statement......if {salary}>0 then {salary}/{increase amount} and for feilds that have no value entered {Salary} I get the "Divide by Zero" Error when I preview the report....is there any tricks or work arounds for this
Snookless

 

[pelajhia]

if {increase amount} > 0 will be the better condition to use - that is what you are dividing by, and if it's zero, you'll get that error.

 

[SurfingGecko]

Try changing your formula to catch a zero value and assign it. You need to avoid dividing by a zero value

...if {salary}= 0 then 0
else
{salary}/{increase amount}....

that kind of thing. Give the formula a way out.

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease

 

[Ngolem]

sorry Gecko...You are not correct here....{salary} is not the bad guy...you can legally didvide into zero...it is {increase amount}....it is a division by zero error

but you are correct that a value , usually zero is assigned if the error is encountered

pelajhia - The condition should not be that the {increase amount} be > 0....negative numbers are no problem

If {increase amount} <> 0 then
{salary} / {increase amount}
else
0;



Jim Broadbent

 

[SurfingGecko]

My Bad

Thanks for jumping in Jim, silly oversight.

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com

Home of Crystal Ease

 

[synapsevampire]

Just so this is fully fleshed out, use:

If not(isnull({increase amount}))
and
{increase amount} <> 0 then
{salary} / {increase amount}
else
0;

If {increase amount} is null without the above, it will still do the division and return null, rather than zero.

-k

 

[Ngolem]

ok SV - Let's Bullet proof it 100% and make sure the other value is not NULL also. No use in half a bullet, eh

If not isnull({increase amount}) and {increase amount} <> 0 then
(
if not isnull({salary}) then
{salary} / {increase amount}
else
0;
)
else
0;

I have never done 2 NOT NULLS in one IF...safer this way


Jim Broadbent

 

[synapsevampire]

Good point, Jim, though simplify.

If not(isnull({increase amount})) and not(isnull({salary})) and {increase amount} <> 0 then
{salary} / {increase amount}
else
0

Probably even a bit more simplification possible...

-k

 

[Ngolem]

I had it that way first but sort of wondered what would happen if the second was not null ...never had 2 NOT Nulls in a row before....probably ok though

Jim Broadbent

 

[Dukester0122]

I have a formula below that returned the Division by Zero error. After reviewing all your responses above I tried changing my formula. Now I get The ) is missing, here's my formula:

if not(isnull({LaborFixed}) + ({LaborVariable})
and not(isnull({EndQTY})) and {LaborFixed} + {LaborVariable} <> 0
then {LaborFixed} + {LaborVariable}) / {EndQTY}
else 0

- Dukester

 

[Ngolem]

this formula won't work even if you put the proper &quot&quot; in

********
if not(isnull({LaborFixed}) + ({LaborVariable})
and not(isnull({EndQTY})) and {LaborFixed} + {LaborVariable} <> 0
then {LaborFixed} + {LaborVariable}) / {EndQTY}
else 0
*********

if not(isnull({LaborFixed}) + ({LaborVariable})

this makes no sense....adding a boolean result to a variable??

All you want to do is check if these variables are null or not and {EndQTY}is not zero

I is DIVISION by ZERO that is the problem....NOT Division OF zero.....you don't check the value of {EndQTY} here????

this is the formula you should use

WhilePrintingRecords;
if not Isnull({LaborFixed}) and not Isnull({LaborVariable})
and not(isnull({EndQTY})) and {EndQTY} <> 0 then
{LaborFixed} + {LaborVariable}) / {EndQTY}
else
0;

NOW I AM ASSUMING HERE that these are database fields. (LaborFixed}, {LaborVariable},{EndQTY}.

You don't use a format that would cause me to believe this such as {table.LaborFixed} so that is why I say I ASSUME this. If this is not true there may be a slough of other issues to deal with.


Jim Broadbent

 

[Dukester0122]

&quot;DIVISION by ZERO that is the problem....NOT Division OF zero&quot; - what the heck are you talking about.

 

[synapsevampire]

Try:

whileprintingrecords;
numbervar LF := iif(isnull({LaborFixed}),0,{LaborFixed});
numbervar LV_LF := iif(isnull({LaborVariable}),0+LF,{LaborVariable}+LF);
numbervar EQ := iif(isnull({EndQTY}),0,{EndQTY});

if {EndQTY} <> 0
then LV_LF / EQ
else 0

This allows for just one of Laborfixed or LaborVariable being null, the previous examples only checked if both are null, so if one had a value, your formula would still place 0 as the result, which might be wrong.

As a cheat, you can select File->Report Options->Convert null value to default and forego null tests as Null will now be 0 in the above. Depends on whether nulls have a special meaning in your report, because all values will now use the default.

-k

 

[Ngolem]

**************
&quot;DIVISION by ZERO that is the problem....NOT Division OF zero&quot; - what the heck are you talking about
****************************

here is your formula...

****************************
if not(isnull({LaborFixed}) + ({LaborVariable})
and not(isnull({EndQTY})) and {LaborFixed} + {LaborVariable} <> 0
then {LaborFixed} + {LaborVariable}) / {EndQTY}
else 0

****************************

You are checking that {LaborFixed} + {LaborVariable} <> 0

who cares if it is zero.... 0/1 = 0 there is no math error here

You are not checking to see that {EndQTY} <> 0 .... that is a potential problem .... 1/0 = division by zero problem

That IS &quot;What the Heck&quot; I am talking about....


Jim Broadbent

 

[Dukester0122]

I'm sorry but synapsevampire's formula already solved the issue so keep your arguments to yourself and next time read the thread properly before saying something stupid, you're losing your credibility to the forum!!!

 

[Ngolem]

Duke -

Just defending my response from those who are not understanding the problem

My credibility is shown in the voting thankyou.

Jim Broadbent

 

[Dukester0122]

Sorry didn't vote for you.

 

[pelajhia]

&quot;I'm sorry but synapsevampire's formula already solved the issue so keep your arguments to yourself and next time read the thread properly before saying something stupid, you're losing your credibility to the forum!!!&quot;

I would personally have to say that this seems pretty rude, and further, that if ngolem is loosing any credibility in this forum, he's got lots left by my book to spare. He's one of the most polite and clever folks posting.
I understand being confused by a response or even thinking it's outright wrong, but dashing off a sharp response usually doesn't help anything.