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.

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

I would put the expression in the Report's RecordSource query.

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

 

[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

 

[Pat Hartman]

No. You need to modify the RecordSource property on the Data tab. It may currently be a table name, query name, or sql string. If it is a table or a string, you need to change it to a string that selects the columns you need and calculates the additional values. The example I posted was simply an example. You need to change fld1, fld2, etc to the column names in your tables. If the RecordSource is already a query, open the query and modify it to add the calculated columns. Sometimes it's easier to switch to sQL view to do this.

 

[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