Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2004
    Posts
    156

    Unanswered: Single, Double, & Other Fraction Data Types

    Happy Holidays All!

    I had some questions concerning number data types that can use fractions. In some situations, I have a problem where a value stored as Single data type in a table that is used in a function (something as simple as Sum in a query) will cause the output of the function to be different than what it should be.

    For example, Sum (12.01 + 15) = 17.01, right? Sometimes, this function would give 17.010000000521, or something to that effect. This seems to only happen when there are 2 or more digits after the decimal place. This, I believe, happens on Double values, as well.

    Does anyone have any comments on this or explanations why? Thanks!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  2. #2
    Join Date
    Jan 2003
    Posts
    81
    I have noticed this too. It seem to only happen though when the result has a larger storeage size (Double 8bit) than the input (Single 4bit) and the input has decimals. It could be a decimal translation issue converting up in storage size relating to the floating point in the Single and Double types because you don't get it with the Currency type.

    Well, it's a theory...

    Bjorn

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    It is obviously important to keep all data types as similar as possible. This is also true during calculations. You may find that using the CSgl() function (for Single) or CDbl() function (for Double) within your calculations will usually solve this problem.



  4. #4
    Join Date
    Jul 2004
    Posts
    156
    The situation in my case is that they're both Single values. And the example I gave above is definitely not larger than the Single's memory allocation.

    Otherwise, I'm just using hard coded numbers versus single values stored in a table. I will look closer at it to see if there is, in fact, another data type involved in the function. However, the only possibilities would be Integer, Currency or Byte. These are the only numerical data types in my database.

    This has bothered me for quite some time and I haven't gotten an answer from someone who says, "Oh, it's definitely this and this and that. That's why it does it." *shrug*
    DocX

    The teachings of God's Begotten: 2 John 1:9

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you can't live with fluff in the least significant decimals, you cannot use floating-point numbers - they just happen to work that way and you get fluff. life's like that.

    if you can survive with only max 15 places before and max 4 places after the decimal - try Currency. the Currency data-type is a scaled-integer and doesn't generate fluff in it's tail.

    if 15.4 doesn't suit, the Decimal data-type is also a scaled integer (0...28 decimals). ...but be prepared to work harder.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh, and if you want the "That's why it does it" stuff...

    ...you could try expressing 0.1 in binary (it repeats infinitely)

    ...or visit here which is actually a site about something completely different but gives a fair explanation of the problem and some java demos of float imprecision to play with.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jul 2004
    Posts
    156
    Well, I'm not too worried about the "fluff" at the end when it's just a few values joined together. I'm just concerned about summing hundreds or thousands of values in a query and having that affect my answer, know what I mean? Especially if you wanted an answer that carried to several decimal places. Another incidence that it comes out in is if the value is placed in a text box and you click in the box, the whole number is displayed. At least, it's happened sometimes. That could be confusing to an end-user. I'm new to programming. I didn't even consider that things like this happened!

    When you pointed out that doing decimals in binary causes the problem, it hit me like a ton of bricks. I think I'm fairly adept at mathematics, but, like I said, I'm fairly new to programming so I didn't put 2 and 2, er, 1 and 0 together.

    I sometimes have a hard time accepting things until I understand WHY they are. Thanks, izy, for the explanation.

    Couple more questions: Why did you say that using Decimal would make me work harder? I know it would make my databases larger. Is that what you meant? That the DB would work harder? Also, can I remove the dollar sign from the Currency data type when displaying values? That might be a way to go.

    I'm a biochemist at heart and degree. I've been doing databases until I start professional school. So, I've been trying to piece together the whole knowledge of computer science for the past year to understand programming. It's a large field. So far I've pieced together.....one piece of the puzzle. And it's like the blue puzzle piece in a picture of a city's skyline.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    work harder: i meant you-the-coder.

    i confess i live happily with the four decimals of the Currency data-type.

    the books say (for my A2K at least) that you can't
    Dim myDeci as Decimal
    ...Decimal is some sort of subspecies of Variant.

    i have never played with the Decimal beast so i can't give any more details.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jul 2004
    Posts
    156
    Does the Currency data type always show up with the "$" in front of the number? Do you have to deal with that every single time you create a field/text box based on the Currency data type?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No not always, occasionaly it comes up with £ in the UK!

  11. #11
    Join Date
    Jul 2004
    Posts
    156
    But of course!

    So the dollar sign is a format property that is automatically turned on in forms and such but can be taken off?
    DocX

    The teachings of God's Begotten: 2 John 1:9

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i was bored and browsed thru some older stuff: did you resolve this yet?



    there are two "currency" things:

    the currency datatype - it knows nothing about $, £ etc but it manages four decimal places with absolute precision.

    the currency format - it knows nothing about precision but obeys your windows regional settings currency preferences as far as currency symbol, thousands separator, and decimal separator. you can display any number (e.g. double) using the currency format

    if you use a currency datatype with the standard format you get no currency symbol.

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Jul 2004
    Posts
    156
    Thanks, izy! Then I have no reason to use anything else but currency for my decimal numbers.
    DocX

    The teachings of God's Begotten: 2 John 1:9

  14. #14
    Join Date
    Jul 2004
    Posts
    156
    Since I've tried it out, as I thought I'd noticed before, I'm going to have to put Standard format on any field in a form that uses Currency data type. This is what you said. I wish it didn't automatically format it with the $ symbol. Oh, well. The price you pay.

    Do you know of any way of turning this automatic formatting off? Thanks!
    DocX

    The teachings of God's Begotten: 2 John 1:9

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can set the format to Standard for a DataType:Currency at table level: subsequently placing the field on a bound form displays without the $/£

    izy
    currently using SS 2008R2

Posting Permissions

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