Garfy
asked on
URGENT!! Formatting Decimal places in pivot tables
I have a pivot table that is bringing in a number. The pivot table is based on a query. I have formatted the decimal places property on the query and on the design of the form (for the pivot table) to 3 places. However, when I view the field on the pivot table it expands out to five places. Any ideas how I can fix this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In above comment, AmountField is alias name for [Amount]. You can do something similar on report or form as well.
Say you have txtAmount with ControlSource set to filed [Amount], make this field invisible,
Add a new text box called txt_Amount with its ControlSource set to:
=Format(txtAmount,"0.000")
Mike E.
Say you have txtAmount with ControlSource set to filed [Amount], make this field invisible,
Add a new text box called txt_Amount with its ControlSource set to:
=Format(txtAmount,"0.000")
Mike E.
It looks like your field is set up goofy in your orginal table. Check the format of the orginal value in the table.
if you want to include ,s like in 1,568,000.120
use
=Format(txtAmount,"##,##0.
use
=Format(txtAmount,"##,##0.
Garfy,
Q. Do you mean to say when you SELECT the field in question that you then see 5 decimal places? And that, otherwise, it displays correctly?
I experience this, too, for numeric and date fields. The numeric field may say 2 decimal places but when I click in the cell the number expands to many more decimal places. Also, when it comes to date fields and if I have saved Now() in the field, I will first see the date as I like it at 2/22/56. But, when I click in the field I may then see 2/22/56 08:30:20 AM or something close to that.
Robert
Q. Do you mean to say when you SELECT the field in question that you then see 5 decimal places? And that, otherwise, it displays correctly?
I experience this, too, for numeric and date fields. The numeric field may say 2 decimal places but when I click in the cell the number expands to many more decimal places. Also, when it comes to date fields and if I have saved Now() in the field, I will first see the date as I like it at 2/22/56. But, when I click in the field I may then see 2/22/56 08:30:20 AM or something close to that.
Robert
Just found this out from the help, not sure if it will help...
Note: The DecimalPlaces property setting has no effect if the Format property is blank or is set to General Number.
If you set the Format property to 'Fixed' it should fixed!
Note: The DecimalPlaces property setting has no effect if the Format property is blank or is set to General Number.
If you set the Format property to 'Fixed' it should fixed!
You will still get the problem that rgcole mentions that when you enter a cell, all the hidden decimal places appear. To avoid this you would need to do as eghtebas suggests and change the Field in design view to something like...
AliasName: Format(TableName.FieldName
By the way, your new alias name can be the same as your origonal field name as long as you qualify the field name with its table name (as above) so...
Field1: Format(Table1.Field1, "0.000")
...will work but...
Field1: Format(Field1, "0.000")
....will not.
AliasName: Format(TableName.FieldName
By the way, your new alias name can be the same as your origonal field name as long as you qualify the field name with its table name (as above) so...
Field1: Format(Table1.Field1, "0.000")
...will work but...
Field1: Format(Field1, "0.000")
....will not.
also consider the data type issue:
1- Format([Table1].[Field1],"
you need to perform further arithmetical calculations.
2- Val(Format([Table1].[Field
1- Format([Table1].[Field1],"
you need to perform further arithmetical calculations.
2- Val(Format([Table1].[Field
'
AmountField:Format([Amount