Pivottable(Form) – Data Formatting

Topic

New Reply

I’ve successsfully created a form with a PivotTable view. All is working they way I would like except for the numeric formating of data totals. I’m using the “Standard” format which is providing a default 2 decimal places. I have yet to figure out how to set the display to 0 decimal places. I did a Lounge search and didn’t find anything on point, but being new to PivotTables I probably used the wrong terms.

Thanks for the help,
Marty

Reply | Quote

Replies

Pivot Tables are based on forms and are essentially a different view of the form. If you set the formatting you need for the textbox in question it should appear in the right format for the pivot table. Try setting the format for the textbox to fixed and the number of decimals to 0.

Reply | Quote

Thanks for the input. I modified the format for the two controls and set the decimal places to 0. But that doesn’t seem to carry through to the PivotTable display as the display is of a count of the one control and a sum on the other. Also tried setting the format in the underlying query which didn’t work either.

Reply | Quote

Thanks for the input. I modified the format for the two controls and set the decimal places to 0. But that doesn’t seem to carry through to the PivotTable display as the display is of a count of the one control and a sum on the other. Also tried setting the format in the underlying query which didn’t work either.

Without seeing a sample version of what you are trying to do or a screen shot, I can’t add a lot more except to say that you can right click on the field in the pivot table and change formatting there as well. Otherwise, you would have to post a zipped copy of the database with confidential info removed.

Reply | Quote

Thanks for the input. I modified the format for the two controls and set the decimal places to 0. But that doesn’t seem to carry through to the PivotTable display as the display is of a count of the one control and a sum on the other. Also tried setting the format in the underlying query which didn’t work either.

Pivot tables in Access (pretty awful, but that’s a subjective opinion),
anyway. You have to do it in Pivot table view.

In 2003 select the field, then right mouse click and select properties tab
In 2007 select field label and then properties sheet icon on pivot format ribbon.

See attached image of the properties box.

Then the Format Tab and change the number style.
Note… No decimal place option.

Not a lot of change between 2003 and 2007 either.
Only available on forms

All in all pretty feeble attempt by Microsoft in light of the Excel version.

Reply | Quote

Andrew,

You’ve answered the question … I can’t set the decimal places in the form design … bummer. Is there a way I could do it with code in say the OnLoad event?

Marty

Reply | Quote

Andrew,

You’ve answered the question … I can’t set the decimal places in the form design … bummer. Is there a way I could do it with code in say the onload event?

Marty

You can do it with something like this.
You need to change the name of the field to fit your pivot table.
Note you will get no help at all from the VBE

This is valid in 2003 and 2007 (It is different in 2002)

Code:

Private Sub Form_Load()

‘You need to provide the name of the Field you use.
‘Also set the required decimal places in numberf format
‘e.g. “0.0”  or “#,##0.0” or “£ 0.00”  etc
‘Note also that usually Fieldset and Field are the same 
‘but it depends upon the layout so you’ll need to play around with it

With Me.PivotTable
    .ActiveView.FieldSets(“Cost”).Fields(“Cost”).Numberformat = “#,##0.0”
End With

End Sub

SO… Why is not on the properties dialogue box?

Poor show MS…….!!!

Reply | Quote

I’ve just stumbled across another approach. Turns out in the Proereties->Format->Number droplist control, you can type or past a Excel like format instead of picking a generic name from the list. So #,##0;(#,##0) solves my problem!!!

Thanks again for your input.
Marty

Reply | Quote

I’ve just stumbled across another approach. Turns out in the Proereties->Format->Number droplist control, you can type or past a Excel like format instead of picking a generic name from the list. So #,##0;(#,##0) solves my problem!!!

Thanks again for your input.
Marty

WHY DIDN’T I THINK OF THAT!

Reply | Quote