Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query that calculates a percentage. After it funs it looks like this: 4.1542589% but I only want to see the 4.1%. Is there a easy way to change to this in the properties tab?

    Please see the attached to see the outcome.[attachment=90860:SampleData1.xls]

    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Corey,

    me again! There are several options, and using the properties of the field is probably the least flexible. You could use a Round() function, e.g. Round([fieldname],1). Or, you could use the Format() function. I'd suggest Round() for greater accuracy.

    If you want to look at Format(), check out the VBA help rather than the Access help - open a code window and then open help.

    Below is an extract from the VBA help.

    Regards,

    Jules



    Different Formats for Different Numeric Values (Format Function)

    A user-defined format expression for numbers can have from one to four sections separated by semicolons. If the format argument contains one of the named numeric formats, only one section is allowed.

    If you use The result is
    One section only The format expression applies to all values.
    Two sections The first section applies to positive values and zeros, the second to negative values.
    Three sections The first section applies to positive values, the second to negative values, and the third to zeros.
    Four sections The first section applies to positive values, the second to negative values, the third to zeros, and the fourth to Null values.



    The following example has two sections: the first defines the format for positive values and zeros; the second section defines the format for negative values.

    "$#,##0;($#,##0)"

    If you include semicolons with nothing between them, the missing section is printed using the format of the positive value. For example, the following format displays positive and negative values using the format in the first section and displays "Zero" if the value is zero.

    "$#,##0;;\Z\e\r\o"

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    As JulesG has said that are several approaches..and choosing the right one depends a bit on what you are doing.

    If you just want to display the data on a report, you can set the Decimal Places property of the field on the report to 1.
    Regards
    John



  4. #4
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Corey,

    Perhaps you have come to a conclusion with this. If not, here are a couple of things to think about.

    First of all, I took your spreadsheet and turned it into a Table in Access 2010 called "Sheet1" and then I built a query. I removed the Formatting from the 2nd Column so that I could see the raw data. The 3rd Column used the Format Properties. The 4th Column used the FormatPercent Function and the 5th Column used Rounding.

    SELECT Sheet1.Account, Sheet1.[0to5Rank], Sheet1.[0to5Rank] AS UsingFormat, FormatPercent([0To5Rank],1) AS UsingFormatFunction, Round([0to5Rank],3) AS UsingRounding
    FROM Sheet1;

    If you do the same, you will notice some interesting results.

    1st of all, depending on the version of Access you are using, you might want to avoid the Round() function. Until Access 2007 I believe they used "Banker's Rounding" which rounds to the nearest EVEN number. A bit different than what you would ecpect if you are used to using Excel. If you want to use Rounding in those Access Versions you should write you own function to do so.

    Secondly, if you look at the results in Column 3, everything looks fine until you click in a cell in that column. At which point you will notice that you see the entire number Formatting a column like that changes the way the column LOOKS and not the actual value in the column. If you were to do subsequent calculations using these cells, the ACTUAL value would be used, not the displayed value. This can cause quite a bit of confusion as sometimes things appear to total incorrectly.

    Column4 uses the FormatPercent Function. The value that you see will be the value that is used in calculations because the number returned is being processed throuhg a function and giving you a result with only the precision that you specify.

    So...depending on what you are going to do with the results of your query will determine how you want to handle the issue. This should give you some additional infforation to make that decision.

    Happy holidays!

    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Bob Oxford View Post
    Until Access 2007 I believe they used "Banker's Rounding" which rounds to the nearest EVEN number.
    Here is an explanation of Banker's Rounding from Allen Browne
    [attachment=90897:BankersRounding.gif]
    Attached Images Attached Images
    Regards
    John



Posting Permissions

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