Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number or Currency Formats in Access - Which to Use?

    I am using MS Access 2007 on Windows 7.

    When I took classes in Access about a decade ago, my teacher told us that if numbers we enter into our database are going to be used to do math, we should always format them as currency. She had some explanation for this which I no longer recall but which made sense at the time. I have followed her advice, and when the numbers I'm using aren't ACTUALLY currency (they usually aren't), I have simply changed the decimal and leading symbol settings on the field so they display simply as numbers, not currency.

    First question: Was this accurate information (that numbers to be used for math should be formatted as currency)?
    If so, then
    Second question: How do I get a sum field in a calculate query to display WITHOUT the currency symbol? I've never had this problem before Access 2007 but now, even though the numbers in my table and in my regular queries display without the symbol, once total them in a Totals query, there is a currency symbol (in this case a dollar sign) in front of them and two decimal places appear, whether or not there were decimal places in the original data.

    Thank you for any tips.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I guess this is why that recommendation was made. Whether you should follow it or not, really depends on what you are going to do with the numbers. As the example states, the results are accurate up to the 14th decimal place.
    Rounding the expression in the example, you still get a zero: ? Round(100.99 - 50.45 - 50.54,13) would get you zero. So, as I said, it really depends on what you plan to do with the numbers.

    My most recent app dealt with performance evaluation and rankings. A specific request was that all results should be presented with 3 decimal places. All I did was to round any relevant calculation to make sure only 3 decimal places were really taken into account. I used Double numbers and had no issues with it.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    As I understand it the Currency datatype uses a special set or routines called Binary Coded Decimal or BCD.
    Since a Binary number can not represent TENTHS as in 1/10 or 1/100 precisely you will invariably get rounding errors when working with Dollars and Cents. The BCD routines code the Binary values in a special way to avoid this problem. There is a performance penalty but in most cases you probably won't notice it and the accuracy is what really counts anyway.

    Back in the bad old days on my Osborne 1 there was a special BASIC language "Business Basic" which had the BCD routines that were not in MS Basic.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So, it looks like there is not really any reason to use "currency" format if I'm not actually using dollars and cents. Is that right? I don't do any complicated math with the numbers, just addition and subtraction.

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    I never actually felt that need. Using rounding in proper places, for normal usage, allows you to use the other number types without any issues.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by organizer View Post
    So, it looks like there is not really any reason to use "currency" format if I'm not actually using dollars and cents. Is that right? I don't do any complicated math with the numbers, just addition and subtraction.
    The Accountant in me says use them when absolute accuracy of decimal places is required...I remember trying to find that .01 when I was out of balance at the end of the period! Then there was the famous Lotus 123 lawsuit where they were off by .25 Million!

    In the end only you know how important exact accuracy to your application.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by organizer View Post
    So, it looks like there is not really any reason to use "currency" format if I'm not actually using dollars and cents. Is that right? I don't do any complicated math with the numbers, just addition and subtraction.
    The problem with using single and doubles is that Access doesn't actually directly store the number you enter, it stores the exponential that approximates the number you enter. Granted it is a very close approximation, but it can lead to problems.

    for example, if you stored the result of a calculation into a single (or even if you allowed the user to enter a number on-screen), and that calculation produced a number like 14.9999999999, it would probably be presented as 15 on-screen or on a report. But if you tried to run a selection query looking for values >=15, it would not appear!

    I mostly use currency datatypes whenever I have to store decimals. In the above situation, that 14.9999999 would have been stored as 15.0000; so it will appear as expected when I run a selection query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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