Field Size vs. Decimals - How many decimals are stored. 1

[CharlieT302]

Hi folks,

I am not quite understanding the "Currency" format when used with decimals.

The field size of "Single" is supposed to store only 7 decimals. This 'seems' to work as long as the Decimal setting is set at 7 or below.

Current Settings:
Field Size: Single
Format: Currency
Decimals: 10

With these settings, 10 decimals displayed; apparently unaffected by the field size (Nine of those digits had mathematical value. The last digit was ending zero). However, the stored value had 14 digits when I clicked into the field. This I do not understand.

When I changed the settings to:
Field Size: Single
Format: Fixed or Standard
Decimals: 10

10 decimals displayed, but the last four were ending zeros. Only the first six had mathematical precision.

Here are my questions:
1. What is the rule as to how the "Currency" format stores values?

2. What effect does the "Decimal" setting have if it goes beyond the stored decimals in the "Field Size" property?

Thanks

 

[rjoubert]

You can also set your field's data type to "Currency" rather than "Single" with a format of "Currency.

 

[CharlieT302]

Thank you, and I realize that. However, a Currency data type is limited to four decimal places of precision whereas a number data type can store as many as 15.

Besides I am more interested in the "why." I am not understanding why nine decimals (numeric values; not ending zeros) would display even though the "Field Size" setting of 'Single' should only allow 7 numerically significant decimals.

Any one else have any thoughts?

 

[Golom]

The floating point data types (Single, Double) store values as a "Mantissa and Exponent". The mantissa can store about 7 significant digits (not places after the decimal) for a single and about 17 for a double. The exponent can be in the range -324 to +308 for a double and -45 to + 38 for a single. You can specify more decimal places of precision than these limits, as for example in

? format((cSng(1)/cSng(7)),"0.000000000000000")

Which yields 0.142857100000000 even though the correct result would be 0.142857142857143


The currency data type is not a floating point data type (i.e. mantissa and exponent). It is a scaled integer data type. That means that it contains 15 digits to the left of the decimal point and up to 4 digits to the right. Further, currency values are exact within their range which means that, as long as your currency values are closed on the interval
-922,337,203,685,477.5808 to +922,337,203,685,477.5807 (including calculated values) then all computations on them yield exact values.

In contrast, computations on floating point values are generally approximations. Not every possible base-10 number can be exactly represented by a float data type even though it is within the defined interval of the selected data type.

 

[CharlieT302]

Golom,

Thanks for the input, but I think we are getting off target; or I am just not understanding. Again, let me state what happened. Please read below:

"Current Settings:
Data Type: Number
Field Size: Single
Format: Currency
Decimals: 10

With these settings, 10 decimals displayed; apparently unaffected by the field size (Nine of those digits had mathematical value; the last digit was an ending zero). However, the stored value had 14 digits when I clicked into the field."

According to Microsoft, Single (field size) limits storage to 7 decimal places (right of the decimal). How is it then, that 10 significant decimals (not ending zeros) can display if they shouldn't even exist?

And here is the real kicker. I only type in 8 digits. Plus when I click into the field, I actually see 13 to 14 digits (not zeros).

 

[Golom]

I suspect that the confusion arises because of the conversion that's going on between the internal representation of the number (single, max 7 digits) and the display format (currency, up to 15 digits). The conversion routines are probably generating spurious digits to accommodate the currency formatting that really do not exist in the underlying internal single number.

My usual practice is to use floats only when I'm doing "scientific" stuff where I'm more concerned with avoiding overflow when handling very large (or small) values and not so much concerned with accuracy down in the eight or greater digits.

If I am concerned about that eighth digit then I will use either a currency or decimal data type that are stored as scaled interers and are guaranteed to be accurate within their full defined limits.