Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Get the sum of decimal values - weird results

    I have a query where I (if I simply it) have three fields. Points, Number and a computed field (Points x Number /40).
    If Points is 6 and Number is 1, the computed field is 0.15.
    I have 5 rows with the same values.

    I then want to summarize these 0.15 values and creates a new query where I group the rows and try to get the sum of the 0.15 values. In all normal mathematics 5 x 0,15 is 0,75, but in the query the sum is 0,5.

    To test I changed the computed field in the first query to (Points x Numbers /4) and then I am getting 1.5 instead. When Access summarizes this, it delivers the right answer: 5x1.5 = 7.5

    So the question is – can not Access compute values below zero?


    The properties for all fields are Default and 3 decimals, and I have tested several other properties, but no avail.

    I am doing something wrong, but what?? (I am using Access 2003.)
    Grateful for any tip, solution or suggestion.

    regards / Bengt C

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Bengt,

    That is very odd indeed! By any chance, can you make the original data and query available?

  3. #3
    Join Date
    Sep 2005
    Posts
    5
    Thanks for your interest, but I solved it with FormatNumber. I explored the function further and came up with this: Obviously Access is not sure that 0.15 is a number, until you use FormatNumber. And then you tend to treat numbers like in Excel. If you have for example 0.125 in a cell and then format it to have two decimals, 0.13 is shown, but in the background the number is still 0.125. So when you make calculation you get the right answer. But in Access rules what-you-see-is-what-you-get.
    If you use FormatNumber(0.125, 2) you get 0.13, and will you get calculations based on 0.13. So beware - use as many decimals you need for the calculation.

    regards / Bengt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Non programer needs help in decimal numbers
    By Peter O in forum Access
    Replies: 1
    Last Post: 10-07-2008, 12:29 PM
  2. Restricting Results In Reports
    By Hawkx1 in forum Reports
    Replies: 0
    Last Post: 06-24-2008, 09:53 AM
  3. Entering query results in a form
    By marcello.dolcini in forum Forms
    Replies: 0
    Last Post: 04-15-2007, 06:01 PM
  4. HELP!!! - Importing table decimal field truncating
    By Valeda in forum Import/Export Data
    Replies: 0
    Last Post: 12-05-2006, 04:06 PM
  5. Replies: 1
    Last Post: 06-09-2006, 05:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums