# error in report calculated field 2

[doe55]

I have a report with several subreports. On the main report I have a field with the following calculation:
=Sum([SumOfDIV_COST])/[Report]!PC_Shipped_subreport!SumOfSumOfQty_Shipped
This calculates the average claim cost by how many items shipped. This works fine. Query parameters are pulled form a combo box/form. My problem is there is one product code (999) that is not shipped. The user selects more than one code, the report breaks on each code. When 999 is among those selected, the page has #error where the calculation would be. I am at a loss here.

 

[dhookom]

Are you suggesting that the sum from the subreport might be 0? If so, you can try:
=IIf([Report]!PC_Shipped_subreport!SumOfSumOfQty_Shipped=0,0,Sum([SumOfDIV_COST])/[Report]!PC_Shipped_subreport!SumOfSumOfQty_Shipped)

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]

 

[doe55]

That didn't work. The subreport would have data on codes that did ship: Code 466 - 48 shipped, Code 999 - 0 shipped. Would my answer be just to run code 999 alone?

 

[dhookom]

You have lost me. Perhaps you should type a few records as they would show in the report and how you would expect them to total.

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]

 

[doe55]

Sorry if I lost you..let me try to explain.
PC_Summary is my main report. It runs off a query with all info on Product and claims pertaining to that product. In the header I have a subreport (PC_Shipped_Subreport). This is linked to the main report by product code. The subreport runs off a query that totals the number of each product code that shipped. All queries have a criteria prompting for product code. A form/combo box is used for this. When code has been selected, the report runs with a page for each code. There is one calculated field in the report header that calculates the average claim for the product code. It pulls the total number shipped from my subreport. This all works fine. Except for code 999. It does not ship so the subreport shows 0. I can't divide 0 so I get an #error for the code 999 page of the report. Does this help?

 

[GingerR]

doe55-From your explanation in your last post, it seems Duane's suggestion (or some form of it) should work. Since you have this in a header, and are summing subreports, it may not work exactly like Duane suggests, but with a little fiddling around you could probably figure it out.

What you need to do is check first if there is 0 shipped. So you have to figure out how to capture that. Mess around with different methods until you figure out how to get an answer of ZERO for item #999.

Tho maybe not the best answer, maybe you need to do something like:

=iif(dsum("QtyShipped","TableName","ProductID = " & ProductID) = 0,null,{rest of your calculation}...

or some other domain calculation.

g





Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244