# Thread: Single, Double, & Other Fraction Data Types

1. Registered User
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!

2. Registered User
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. Stuck on my opinions...
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. Registered User
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*

5. Cavalier King Charles
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

6. Cavalier King Charles
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

7. Registered User
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.

8. Cavalier King Charles
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

9. Registered User
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
No not always, occasionaly it comes up with £ in the UK!

11. Registered User
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?

12. Cavalier King Charles
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

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

14. Registered User
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!

15. Cavalier King Charles
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

#### Posting Permissions

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