Pivot Table Form - Display Null Values as "0" on Data Axis?

[LDSol]

Hi Everyone,

I have a Pivot Table Form in Access 2003 and need the blank/null cells on
the data axis to display "0". This is easy to do in Excel but I have searched
through all of the options in Access and cannot find it.

What is the VBA code required to set this option?

Can anyone suggest a good reference book or website for coding Pivot Tables
with VBA?

Many thanks,

David

 

[tehNellie]

To replace null:
in VBA:

nz(variable, 0)

in SQL:

isnull(column, 0)

To replace zero length string

VBA and SQL

replace(column,"",0)

To replace nulls and Zero Length Strings

VBA:

replace(nz(column,""),"",0)

SQL:

replace(isnull(column,""),"",0)

 

[LDSol]

Thankyou tehNellie,

If I have a Pivot Table that gives total Sales by Store by Department, not all Stores will have Sales for every Department, this means there will be a blank cell on the data axis.

Can you tell me how to apply this code to the Pivot Table?

e.g.

If I want to hide details on the Pivot Table:

Screen.ActiveDatasheet.PivotTable. _
ActiveData.HideDetails
frm1.PivotTable.AllowDetails = False

If I want to insert Row and Column Headings:

With frm1.PivotTable.ActiveView

Set fst1 = .FieldSets("Store")
.RowAxis.InsertFieldSet fst1

Set fst1 = .FieldSets("Department")
.ColumnAxis.InsertFieldSet fst1

End With

If I want to insert aggregate data on the data axis:

With frm1

Set fld1 = .PivotTable.ActiveView. _
FieldSets("Sales").Fields("Sales")
.PivotTable.ActiveView.AddTotal _
"Sum of Sales", fld1, plFunctionSum
Set tot1 = .PivotTable. _
ActiveView.Totals("Sum of Sales")
.PivotTable.ActiveView.DataAxis. _
InsertTotal tot1

End With

Many thanks,

David