Re: Display of Single Data Exported

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TerryM" <TerryM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:85FE5234-E730-4A65-880B-23AA01A31EB9@xxxxxxxxxxxxxxxx
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

"Allen Browne" wrote:

Change the data type to Currency.

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

"TerryM" <TerryM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:40E13E7C-9599-40DD-B6A6-D0EE7396A4B1@xxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • Re: Display of Single Data Exported
    ... So, Access provides the currency data type, which is not a floating point ... It stores exactly 4 places of precision after the ... Microsoft introduced a scalable point data type - a Number ...
    (microsoft.public.access.tablesdbdesign)
  • Re: DECIMAL v DOUBLE
    ... Double, short for double precision floating point, is a data type based on an IEEE standard for a 64-bit floating point value with 11-bit exponent and 52-bit mantissa. ... the MONEY data type has truncation issues and is of no practical use for any serious computations. ...
    (microsoft.public.sqlserver.programming)
  • Re: Why wont my floats add up?
    ... are floating point numbers -- imprecise. ... If you want precision you have to choose something that does precision: ... We have no idea what the OP is actually trying to do, so it's not possible to know for sure what the best data type is for his application. ... All we can do is point out that whatever the type that is used, there is some limit to its precision, both in terms of the number of digits that can be represented, and whether or not a given number can be represented exactly. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Precision
    ... Whether one does the subsequent calculations in internal ie integer format ... I have always used external format with Precision 4 since my ... floating point calcs which are done in binary have around 3% error albeit at ... positions it is IMPOSIBLE to have a result of 4 decimals with the 2 ...
    (comp.databases.pick)
  • Re: Linear Algebra Challenge
    ... Since I'm using floating point, so I'll never be able to calculate one ... floating point math set to 99 digits. ... As close as I'm willing to wait if I use arbitrary precision. ... This mode is fast; when you select arbitry ...
    (comp.sys.hp48)