Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    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

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    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.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mcowen View Post
    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.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by mcowen View Post
    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.

    [attachment=86737:PivotProperties.jpg]


    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.
    Attached Images Attached Images
    Andrew

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    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

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by mcowen View Post
    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


    [attachment=86739:PivotDemo.jpg]


    SO... Why is not on the properties dialogue box?

    Poor show MS.......!!!
    Attached Images Attached Images
    Andrew

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    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


  9. The Following User Says Thank You to mcowen For This Useful Post:

    bobguest (2012-05-10)

  10. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by mcowen View Post
    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!
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •