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."

"Simka" <Simka@discussions.microsoft.com> wrote in message 
news:CEB20122-CC8D-49D4-8B96-D93C599B99D8@microsoft.com...
>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?
>
> 

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

Al Campagna wrote:
> 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.