Display of Single Data Exported

[TerryM]

I have a simple lookup table containing a list of standard expenses and their
associated costs. The data type of the expenses is 'single' and 'currency'
format i.e. 2 decimal places.

When a table containing expenses is exported to Excel values are shown not
as 2 decimal places but 13. When I return to the lookup table and move to an
entry e.g. 9.82 it displays 9.81999969482422. I've tried several times to
enter the correct value but they always return the incorrect value.

I'm happy with the results of reports etc. as they display the correct value
but the exported files appear misleading.

Any suggestions on how I can resolve my problem please?

Terry

 

[Allen Browne]

Change the data type to Currency.

The floating point types (single and double) cannot store fractional values
exactly.

 

[TerryM]

Thank you that worked.

However, I'm quite stunned by your statement "The floating point types
(single and double) cannot store fractional values exactly".

Is that not the point of using such data types i.e. high precision.

Terry

 

[Tim Ferguson]

However, I'm quite stunned by your statement "The floating point types
(single and double) cannot store fractional values exactly".

This is a question of simple maths...

It's not possible to express 1/3 using decimal notation: 0.3333333333 is
not the same, regardless of how many decimal points you use. The same
applies to sevenths, ninths, elevenths, twelfths and so on.

Similarly, it's not possible to express several fractions in binary
either: unfortunately for us ten-fingered creatures 1/10 is a recurring
number in binary, and that puts the kibosh on lots of arithmetic that
looks easy in ten-base.

There are three workarounds:

Using binary-coded decimals -- this uses normal sixteen-based numbers,
but the numbers A to F are not used and special carry registers make them
work like ten-based digits. You get the answers you expect from tne-based
maths, but not very efficient storage, and the algorithms are not
necessarily bug free.

Use real numbers and accept the imprecision. Stop doing

If a=10 then

and start doing something more like

If 9.99 < a And a < 10.01 Then

Use integers and scale them appropriately. Instead of using a real type
to store Dollars to 2 dp, try storing Cents and formatting the output.
This _I think_ is how the currency data type works except you don't get
to choose what the scale is. I don't know of any currency around the
world that uses ten-thousandths of the basic denomination, so the default
4dp is just effing stupid, if you ask me. Yes, I know you didn't ask me.
Still, rolling your own still gives you proper control.

Hope that helps


Tim F

 

[Allen Browne]

In a decimal system, there are many numbers that would require an infinite
number of decimal places to represent them, e.g. 1/3 or 1/7. That happens in
a binary system as well: many fractional numbers cannot be stored precisely
in 32-bits (single) or 64-bits (double.) This issue is not specific to
Microsoft Access: it is true of floating point numbers in all computer
software.

So, Access provides the currency data type, which is not a floating point
number, but a fixed point. It stores exactly 4 places of precision after the
decimal point (hundredths of a cent.) Effectively, the computer does integer
math with this type, and then adjusts the decimal point after the
calculation. Consequently, there is no floating point error. This is really
useful for currency data, but also for other things where you don't want
rounding errors, such as storing values in meters (the 4 decimal places
gives you tenths of a millimetre precision), kilograms (1/10th gram
precision), and so on.

In Access 2000, Microsoft introduced a scalable point data type - a Number
of size Decimal. Nice concept, but unfortunately, they didn't get it right.
Access cannot even perform a basic sort on this type, so it is really
unusable. More info:
http://allenbrowne.com/bug-08.html

So, Currency is worth keeping in mind of you need fractional value up to 4
decimal places, and don't have to handle enormous or miniscule numbers.

 

[TerryM]

Thanks to you and Tim for an excellent answer. It answers a few questions
that I've head for a while e.g. why BACS uses values in pence (integer)
rather than using Pounds and Pence (GBP).

Terry

 

[David Mueller]

Use integers and scale them appropriately. Instead of using a real type
to store Dollars to 2 dp, try storing Cents and formatting the output.
This _I think_ is how the currency data type works except you don't get
to choose what the scale is. I don't know of any currency around the
world that uses ten-thousandths of the basic denomination, so the default
4dp is just effing stupid, if you ask me. Yes, I know you didn't ask me.
Still, rolling your own still gives you proper control.

I'm not taking issue with your opinion. I'm just adding some background ...

It is worth noting that almost any business involved in making or selling a
product carries their costs (and often price) out to three decimals -
sometimes four. I'm sure financial institutions do, too.

Bolts for example...
$127.56 per hundred (vs two decimals) $128.00
$12.756 per ten (vs two decimals) $12.80
$1.2756 per each (vs two decimals) $1.28

Small example, small amounts, but there is a significant difference as the
quantities and sales numbers get higher and higher.