Divide by Zero ERROR - HELP!!! (1 Viewer)

rkrause · Jul 20, 2009

I have a report Where my detail row is already summed up from my query. So example on of my fields is called SumOfGeneral_Repair.

Ok so in my report footer i have all my detail rows summed up again. I also have a textbox that is counting the rows that do not have a zero. So what i have in my report footer for formula is:
=Sum([SumOfGeneral_Repair])/[text39]

How would i incorparate that formula so i can forget the divide by zero error?

pbaldy · Jul 20, 2009

You can test in an IIf() and only do the division if it's greater than zero.

rkrause · Jul 20, 2009

OK So how would i do that in my formula that i posted?

MSAccessRookie · Jul 20, 2009

rkrause said:
OK So how would i do that in my formula that i posted?

The Syntax of IIf() is

IIf({Condition to Test}, {Value if Test is True}, {Value if Test is False})

Kryst51 · Jul 20, 2009

Iif([text39]>0,Sum([SumOfGeneral_Repair])/[text39] ,Sum([SumOfGeneral_Repair])

Disclaimer: I am not sure if this is correct.

MSAccessRookie · Jul 20, 2009

Kryst51 said:
Iif([text39]>0,Sum([SumOfGeneral_Repair])/[text39] ,Sum([SumOfGeneral_Repair])

Disclaimer: I am not sure if this is correct.

I think you might want to treat the Null or 0 divisor as having a 0 returned value.

Note: the extra set of parenthesis are for clarification only and can be removed if you desire. I like to use them in formulas like this.

IIf([text39]>0, (Sum([SumOfGeneral_Repair])/[text39]) ,0)

Kryst51 · Jul 20, 2009

I thionk you might want to treat the Null or 0 divisor as having a 0 returned value.

LOL, I have never been good at math.....

MSAccessRookie · Jul 20, 2009

Kryst51 said:
LOL, I have never been good at math.....

Did that formula work for you?

Kryst51 · Jul 20, 2009

Did that formula work for you?

Not sure if you were asking me but I didn't test it. My thinking and reasoning skills are a little off today, due to the fact that I am tired..... So my reasoning was wrong when I worked it out in my head.

MSAccessRookie · Jul 20, 2009

Kryst51 said:
Not sure if you were asking me but I didn't test it. My thinking and reasoning skills are a little off today, due to the fact that I am tired..... So my reasoning was wrong when I worked it out in my head.

Once you have worked things out, let us know if you have any more questions.

boblarson · Jul 20, 2009

I wouldn't use

IIf([text39]>0,

I would use

IIf([text39]<>0,

Because what if you have a negative number? It would get bypassed.

tebule · Jul 20, 2009

Use the Nz function. =nz(me.text39/5, 0) if the me.text = 0 then a null value would be the result. with the nz function it will return a 0 in the code I have included.

boblarson · Jul 20, 2009

tebule said:
Use the Nz function. =nz(me.text39/5, 0) if the me.text = 0 then a null value would be the result. with the nz function it will return a 0 in the code I have included.

This will not work. If you have /5 then it will but the problem is if the field is a zero that you are dividing BY.

tebule · Jul 20, 2009

I beg your pardon, boblarson is correct.

rkrause · Jul 21, 2009

KRYST51, worked good. thanks.

Divide by Zero ERROR - HELP!!! (1 Viewer)

rkrause

Registered User.

pbaldy

Wino Moderator

rkrause

Registered User.

MSAccessRookie

AWF VIP

Kryst51

Singin' in the Hou. Rain

MSAccessRookie

AWF VIP

Kryst51

Singin' in the Hou. Rain

MSAccessRookie

AWF VIP

Kryst51

Singin' in the Hou. Rain

MSAccessRookie

AWF VIP

boblarson

Smeghead

tebule

Registered User.

boblarson

Smeghead

tebule

Registered User.

rkrause

Registered User.

Similar threads

Users who are viewing this thread