Access PivotTable based on Query with Calculated Field

[Arj418]

All,

I have a PivotTable in Access that is based on an underlying query. One of the controls in the query is a calculated control. The formula in the calculated control is:

PassRate: 1 - ( CountOfX / CountOfY )

This works fine and displays the proper result in both the query and the PivotTable.

However, if I modify the formula a little and use the following:

PassRate: 1 - ( CountOfX / (0.02 * CountOfY))

then I run into a problem with just the PivotTable, even though the query still works just fine.

Specifically, the query may show a calculated value of .625, but the PivotTable only displays "0".

I can't figure out why the PivotTable can't handle this change in the calculation. The values for CountOfX and CountOfY are not abnormal. A typical CountOfX value would be 30, and a typical CountOfY value would be 4000.

UPDATE: I just determined that the PivotTable works fine if I change the calculated field in the query to:

PassRate: 1 - ( CountOfX / (CountOfY / 50))

So at this point I guess I found a workaround, but I'm still really curious why the PivotTable choked on multiplication, but was fine with division.

Any thoughts?

 

[PHV]

I'd try this:
PassRate: CDec(1 - ( CountOfX / (0.02 * CountOfY)))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886