Is there a way of not showing zero totals in a pivot table?

  • Thread starter Thread starter Simka
  • Start date Start date
S

Simka

I have a table with several thousand records and I have created pivot table
within Access 2003 to analyse this data. The amount field is currenlty
formatted as currency with 2 decimal places.
If two records with an account of 123456 and one record with an amount of
500.00 and the other record also with the same account of 123456 and an
amount of -500.00, and so on the pivot table the total is showing as 0.00
(one entry cancelling the other). Does anyone know if there is a way to NOT
show these zeros in the totals column as there are quite a few totals with
zero values which I do not want to appear?
 
Simka,
See Help on Format Property - Number/Currency Data Types
Try this Format...
#.00 ; -#.00 ; "" ; ""
(I put spaces in to clarify the semicolons... remove in your actual
format)
The third part of this format will cause any 0 values to display as
blank.
--
hth
Al Campagna
Microsoft Access MVP 2006-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
You could also change the query's TRANSFORM clause to return Null if the Sum
is zero.

TRANSFORM IIF(SUM(Amount)=0,Null,Sum(Amount)) as TheSum
SELECT ...

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top