How to Elimate #Div/0! Errors in MS Access Reports

[JNelson0318]

If one of the fields are 0 then it returns this error on my Reports in the Field and/or in the Sum of these. I read somwhere to create a tblDivByZero table but since I'm a novice I still can't make this work. Hopefully, someone can assist me with this.

 

[JNelson0318]

Pat,
I'm new to MS Access...where do I enter this code?

 

[JNelson0318]

New to Access...where would I enter this code?

 

[JNelson0318]

The code needs to be changed to include an IIf() so you can avoid the divide by zero error.

IIf(Nz(MyDivisor,0) = 0, MyDividend, MyDivident/MyDivisor)

I just returned the dividend field when the divisor = 0 or null. You might want to return 0 or null.

Pat,
I added this to my Field:

= (IIf(NZ([Units],0) = 0, [Rate]*[Hours], [Rate]*[Hours]/([Units])

It is giving me the following error:

"The expression you entered is missing a closing parenthesis, bracket(]), or vertical bar(|)"

 

[JNelson0318]

Pat,

Ok finally, I tried this by just copying my Dividend and Divisor used in original Formula:

= (IIf(Nz(Sum([Units]),0) = 0, =Sum([Rate]*[Hours]), =Sum([Rate]*[Hours])/Sum([Units]))

Now I get the following error:

The expression you entered contains invalid syntax.

You may have entered a comma without a preceding value or identifies.

 

[JNelson0318]

Pat,

Can you explain this in more Detail? Do I bring up the report and go to the Source for the Units, Rate, and Hours fields and enter this formula in each of those Fields?

Select fld1, fld2, fld3, IIf(NZ([Units],0) = 0, [Rate]*[Hours], [Rate]*[Hours]/([Units]) As TotCost, fld4, ....
From YourTable;

Sorry to be such a Novice with this...I sincerely appreciate your assistance!!!

Jann

 

[JNelson0318]

Pat,

I think I may have it - getting expression too long on some queries but I'm working on that....I appreciate your help. Also, getting #Num! output for Cost per Load. Would that be the same concept as the Div0 error?

Jann