Results 1 to 8 of 8
  1. #1
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159

    "Single" Datatype Mayhem.

    The following table has two columns, one autonumber and another a Single number datatype. I made this query so I had access to the Sum function. As you can see, it's working just fine here.
    Click image for larger version. 

Name:	normal.jpg 
Views:	25 
Size:	64.4 KB 
ID:	29078

    Here's what it looks like after changing 4 into 4.4. Notice the total.
    Click image for larger version. 

Name:	weird.jpg 
Views:	25 
Size:	27.6 KB 
ID:	29079

    I tried to do some research on this issue, and I see that the first 7 digits to the right of the decimal are always accurate... apparently the Single datatype has a maximum of 7 significant digits. But what I can't figure out is:
    1) Where in blazes are the trailing 6 digits coming from?
    2a) If "Single" can't hold the number 10.4, what on earth can it hold? Why even use Single?
    2b) Is there a way to cut off the other trailing digits somehow? Would you even care to?
    3) Everything works great when I switch to a "Double" datatype. My concern is, once the numbers get complex, will Double have this same problem?

    4) Most importantly: I want a column that will store my formula for a chemical. 28.5% resin, 1.75% catalyst, 55% Powder filler A.... what datatype is ideal for this? never more than three digits to the right of the decimal.

    Thank you for any insight. Sorry if the inline graphics fill up your entire screen, not sure why screenshots scale like that on this forum,


    Matt

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I suggest 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 maximum 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.

  3. #3
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    thank you. I will do that.

    Does anyone yet know why Single behaves the way it does? I assume something to do with it's binary representation, but why would anyone use it? I'm afraid of Single now.

    matt

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I assume something to do with it's binary representation
    That's right - it does. In binary representation (which everything is in a computer), the exact value if integers can be stored with no problem, just as they can in our familiar decimal notation. However, when it comes to decimals, very few numbers can be represented exactly in binary. Only values that are made up of some combination of 1/2, 1/4, 1/8, 1/16 ..... can be represented exactly. So, even our lowly 1/10 or 0.1 cannot be represented exactly in binary. It's the same as trying to represent 1/3 in decimal - 0.33333... You can use as many 3's as you like, but you'll never get the exact value.

    What you are seeing with that 953674 is that lack of "exactness" in binary. A "Single" type number in Access has only the binary equivalent of 7 decimal places precision, and "Double" is 15.

    ...but why would anyone use it? I'm afraid of Single now.
    It depends on how precise your calculations need to be - for most purposes, single is fine. The data in it is not "wrong", as one might think from your illustration.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to John G's description of representation, see single in this link

    Here is FMS' take on Decimal

    Do you need more precision?

    Here are various Rounding functions (originally in vb5/6 )

    Good luck

  6. #6
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Thank you for all your answers, but now I'm scared of both Decimal and Single data types, heh.

    My biggest concern right now lies in that I only need 5 or 6 significant digits, yet Single still will not work! Here's why: I want my users to enter custom formulas where they can have 10% of ingredient A, 15% of ingredient B, 1.75% of ingredient C, etc. etc. So far, you're probably thinking "Single data type should work just fine - it has more than enough resolution for such small numbers." Where the story takes a twist is when I run a subroutine that checks whether the users formula adds up to 100%, because it always should (just to be sure they don't miscalculate). So I add up the value of every field and it LOOKS like 100%:
    Click image for larger version. 

Name:	www.jpg 
Views:	18 
Size:	49.8 KB 
ID:	29093

    Notice the red text. As you can see, a normal person might think this is perfect job for Single datatype. They would be wrong because there is a string of inaccurate binary representation hiding from view behind each of those numbers. I don't understand: Why on earth would Single try to record digits that it knows it cannot handle? It tries to handle all 15 decimals, even though it's destined by design to fail.

    Again, it all goes away if I use a Double, but I don't have the imagination to envision whether this same problem can occur with Double if (in a different project) the numbers get more complicated. I don't know... maybe it will try "under the hood" to record 23 digits to the right, but is only outfitted to handle 15, thus the same kinds of problems recur. I feel like I don't trust working with decimal precision anymore.

    Tell me Double immune to all this!
    Matt

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Tell me Double immune to all this!
    I can't tell you that, because I would be lying. If single can't store a number exactly, then double can't either. The phenomenon you are experiencing is called "rounding error". The reason you are getting the warning message is that you are comparing the sum of the percentages to 100.0 . 100.0 can be represented exactly in binary (because there are only zero's after the decimal point), but the sum of your percentages cannot be represented exactly (because you are summing inexact values), so when you make the comparison, they are not equal, even though it LOOKS like they should be.

    This problem has been with us since the beginning of the computer era - comparisons which test for equality between non-integer values can NEVER be relied upon. What you have to do is test for "equality" withing a tolerance you decide upon. In your case, 4 places after the decimal should do, so you can use an expression like this:

    if Abs(100.0 - Sum_Of_Percentages) > .0001 then Display the warning message.

    In other words, if there is a difference between your sum and 100.0 within the first 4 decimal places, there is an error.

  8. #8
    MatthewGrace is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    159
    Thank you for your input and example expression. I got it now.

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

Similar Threads

  1. Replies: 7
    Last Post: 02-12-2017, 06:42 AM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 1
    Last Post: 09-03-2014, 03:27 AM
  4. Replies: 3
    Last Post: 05-13-2014, 01:55 PM
  5. Replies: 11
    Last Post: 03-29-2012, 02:32 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