Formatting Issues with Pivot Tables

[alex35]

I am using Access 2000 and attempting to use a Pivot Table. I am able to get this to work except there are some formatting issues.

Specfically, I would like to format the table as follows:
- Zero "0" will appear in the cells where no values are present

- Totals with commas for the thousandth place and without a 00 appearing after the decimal place.

Below is how the table is currently displayed:

Handling
FY Category P J Grand Total
1997 1st Cat 1,000.00 700.00 1,700.00
2nd Cat 300.00 300.00

Total 1,000.00 1,000.00 2,000.00

2001 1st Cat 500.00 100.00 600.00

Total 500.00 100.00 600.00

Grand Total 1,500.00 1,100.00 2,600.00

 

[gol4]

would need to see the exact syntax you are using in the sql but I would think that changing your value filed to read something like

=nz(int(thevaluefield),0)

nz will return 0 for null, int will drop the .00
and the commas should be automatic if not look at the format function as well