Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    SELECT fieldA - fieldB same value (big number) returns not 0


    I use SELECT statement of field A - field B of a 15 decimal points number. The two number are the same, however the return is not 0
    but if I put it in Excel and minus the two they are 0. Why is that, how can I make Access show 0 ?

    example:
    Click image for larger version. 

Name:	ex.GIF 
Views:	15 
Size:	6.8 KB 
ID:	32672

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    you now see the issue with floating point numbers.

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I see, how can I make it in the SELECT statement in Access that when the number are the same (no matter how big the numbers are) the difference is 0 ?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if I put it in Excel and minus the two they are 0
    my bet is that they are not the same in excel either, but you have formatting so it appears to be 0. Try the same comparison in Excel as you are using in a query (a=b) and see if it returns true

    how can I make it in the SELECT statement in Access that when the number are the same
    the point is, they aren't the same, only what you can see/can be displayed is the same. The difference is very small, if you don't need that level of accuracy, then use the round function or a different datatype such as currency (which is limited to 4dp)

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    In excel when I do a = b it returns true, and the difference is
    0.0000000000000000000000000000


    when I copy the return from the Access query, I get:
    -0.0000000000000003330669073875

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What is the source of these two number - is it SQL linked table or an access table, and is it a comparison of a DateTime by any chance?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    No just type in any big random number manually.

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As RuralGuy said - you are suffering from Floating Point differences - read here for some more detailed explanations http://www.consultdmw.com/floating-point-numbers.htm and here http://sqlanywhere.blogspot.co.uk/20...ing-point.html
    Last edited by Minty; 02-21-2018 at 10:37 AM. Reason: Credited the wrong responder!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you have no control over the table fields you can just use a function to eliminate your issue.
    i.e. Convert the field to a string and then truncate the string down to the resolution you need, just for comparison.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try using Decimal and specify scale and precision properties in the table.
    Precision is the total number of digits in a number, including left and right of the decimal.
    Scale is the number of digits allowed to the right of the decimal.


    So 1234567.89 has a precision of 9.
    A precision of 4 and scale of 2 would result in a number with a maximun value of 99.99
    123456.789 has a scale of 3 and precision of 9.


    Percent designations like 125.5% would require precision 4 and scale of 3 (not one) since it is actually stored as 1.255.


    Precision has a maximum value of 28, but textboxes in forms allow only a max of 15 decimal places.
    For small numbers like yours, try both scale and precision as 18.

  11. #11
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    RuralGay, could you show me an example ?

  12. #12
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    davegri - right now for the table I set all the fields with number as number, double, general and decimal point as 15 the maximum.

    I see and understand the floating point problem, that the number can only be shown 16 decimals but because of it can be so detail and precise and if goes over this, the difference are very small that even though two number we see as equal and the same show very very tiny difference.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by johnseito View Post
    RuralGay, could you show me an example ?
    It is going to be something like:
    Left(Str(FieldA),5)
    You could even use Val(Left(Str(FieldA),5)) if you wanted.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    right now for the table I set all the fields with number as number, double, general and decimal point as 15 the maximum.
    Did you try my suggestion in post #10?
    If you did, you would see that the difference between the two numbers you show in post #1 is ZERO.

  15. #15
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Quote Originally Posted by davegri View Post
    Did you try my suggestion in post #10?
    If you did, you would see that the difference between the two numbers you show in post #1 is ZERO.

    I don't see scale and precision property in the table.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-16-2017, 04:55 PM
  2. Replies: 1
    Last Post: 05-26-2014, 03:02 AM
  3. Replies: 0
    Last Post: 03-25-2011, 03:35 PM
  4. Show only rows where fielda < fieldb
    By Schon731 in forum Queries
    Replies: 6
    Last Post: 12-02-2010, 06:26 PM
  5. Replies: 3
    Last Post: 07-08-2010, 01:47 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