Division by Zero at the Footer / Summary Band

[Tamrak]

I read the past posts regarding the Division by Zero. However, it could not resolve my issue.

Let's say I have a field called, FTE. I have six records. Some of the records have blanked this field out.

ID# FTE

1234 (4.0)
1235 (3.0)
1236
1237 (4.5)
1238
1239 (5.0)

* I am using parenthesis to make it easier to read, not to apply any negative value.


I would like to sum or average my calculation on the group footer. I have not created any additional field from the query. However, I created a calculated field on the report, using the NZ function. It gave me the "division by Zero" error message.

Question:

1. How can I sum the FTE field and average them? Will the "=iif(NZ(sum(..... )" work?

2. For the average, how can I ensure that the blank will be omitted? In this case, the divisor should be four (4) records.

3. Do I need to create another hidden field to count the "FTE" field in the detail band? In the real situation, I have more than 20 fields.

Thank you for your help.

 

[jjb373]

Tamrak,

First, I suggest adding the report header and footer to the report. (Properties --> select Report Header/Footer option)

Best reporting practices:
Total calculations for the report should be in the report footer. Unless you have numerous groups, each having the sum calculations, average calculations, etc...then add it to the group footer.

Question 1 Reponse:
=IIf(Nz([FTE],0),Sum([FTE]))

Question 2 Reponse:
If you use the AVG formula you can be sure the divisor will not include blanks because you are not setting null equal to anything. Basically the formula only works with what is showing in the report.
=AVG([FTE])

Question 3 Response:
What are you wanting to count?


Cheers,

JJB373

 

[dhookom]

=Sum([FTE])
=Avg([FTE])
There is no need to count unless you want to.
=Count([FTE])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tamrak]

Good morning,

Thank you for your inputs.

I am still having problem. However, I think I know why it did not work.

1. The example that I provided the first time, used the query as a property, not table.

2. The FTE field is the result of the calculated field. I used the table to create that query. For example, FTE came from a calculated field based on something like this: ([Test1]*[Test2])/[Test3]. I think they have some effects on the report.

3. When I run the query, it provided some blank information in the FTE field. I thought it was fine.

4. I decided to group and sum them. Then, I received an error message regarding Division by Zero.

I think I might be able to sum the FTE field by going back to the calculated field. If you have suggestions, please kindly post them. Thanks.

 

[dhookom]

You would need to calculate FTE like:
FTE:IIf([Test3]=0,0,([Test1]*[Test2])/[Test3])

I have no idea what you are doing with "group and sum" like if this is in a query or report or what...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]