Re: Rounding info needed-A2k

Okay, I'm still in the dark. I've read your response and KIND of
understand. The link about 'accuracy problems' lost me at my click of it.
::grin:: (math major, I'm not!)

And, I've read the discussion between you and FMS.

However, I've still got a problem, as I see it.

I've got an operator who enters a payment to an account of 1353.57. That
amount is stored in a Number field with a Field Size of Double. Decimal
Places = Auto. It's not divided by anything...just entered as 1353.57 and
my program multiplies by -1 so it's a negative number and on to the next
payment. But, instead of saving the entered amount -1353.57, it's stores
something like -1353.569999999999999988832982929828 (etc).
Or, -1353.570000000000000000000002737387237732 (etc).

Who decided it should be something other than -1353.57? The data type? Or,
did my multiplying the input number (a positive number) by -1 do the nasty
deed of making it a minus 1353.5699999999999(etc)?

And how do I get it stored as -1353.57 (exactly as input rather than this
bazaar desire of whatever to be so accurate I could use it to build a DNA
example)?

I'll NEVER need a field saved that's the result of dividing something by
something else (ex 1353.57 / 9 = 150.841111282817171761 or whatever).
Perhaps in a report I'd do something like that, but I'd not need to save it
anywhere (as far as I know). And, if I do it in a report or whatnot, I can
see doing the round() function now that I know of this bazaar desire to be
accurate out to 82 million digits (or, whatever). But, all I'm trying to do
is store an accurate negative amount (accurate to me is what was input) that
is, in fact, dollars and cents.

I chose the number/double type over the currency because I didn't like the
dollar signs appearing all over the place. Perhaps that was my error? I
now realize I could have used the currency type and then standard format, or
whatever. But, it's a done deal...unless I change the type, which I guess I
could do. But, is that necessary to get what I'm trying to get?

Second to the last paragraph of your response seems to indicate I'll still
need to do the round() function prior to saving the data input to the
approrpiate field. Where do I do that?

Thanks much for all the info...and sorry I'm seeming so dense (perhaps I
am...).
ron

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:472a6954$0$17164$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
There are a couple of factors that contribute to this issue, Ron.

The first is the field type. If you use a Number field (size Double or
Single), you have a floating point data type. This is really useful for
handling enormous numbers (+/- numbers with millions of billions) or
miniscule numbers (hundreds of zeros after the decimal point before the
digits really start), but they are limited to around 15 significant digits
(for a Double.) Most fractional numbers cannot be stored precisely in this
binary form, just as many numbers (such as 1/7 or 1/3) cannot be stored
precisely in the decimal form without an infinite number of digits.

Consequently, floating point numbers always have these rounding problems.
For more details, see:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

One solution is to use a fixed point data type instead of a floating
point. The Currency type in Access does this. It stores the number as if
it were an 8-byte integer, and then adds the decimal point 4-digits from
the right. This arrangement means that the 4 digits to the right of the
decimal point are precise. The disadvantage is that it is not suitable for
numbers where more than 4 digits are required, or for really enormous
numbers. This is ideal for most currencies. It is also useful for other
scenarios (e.g. to store precise distances in metres, with a resolution
down to 1/10 mm.)

Your example wanted only 2 decimal places. The Currency format typically
shows 2, though it stores 4. Therefore you will need to use Round() when
performing calcuations that write a value so the Currency field, so ensure
the last 2 digits are stored as zeros. If you want to display the currency
field as fixed to 2 decimal places (not dollars and cents), set its Format
property to Fixed, and the Decimal Places to 2.

There is another data type in Access: a Number field of size Decimal. This
is a scalar data type. It is similar to the fixed point, but you can
specify where the decimal point goes for any particular field (unlike the
Currency field, where it is always 4 digits.) While this is a great
concept, unfortunately the implementation in Access is buggy, so I can't
recommend using it. Here's one example of why:
http://allenbrowne.com/bug-08.html

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

<snipped my original post>


.

Relevant Pages

  • Re: Rounding info needed-A2k
    ... digits really start), but they are limited to around 15 significant digits ... floating point numbers always have these rounding problems. ... One solution is to use a fixed point data type instead of a floating point. ... The Currency type in Access does this. ...
    (comp.databases.ms-access)
  • Re: Best datatype for money
    ... >> Assuming you mean Java data type, ... >> specialised money data type, ... >> to represent an interest rate or a currency conversion as precisely. ... > just specify the number of fractional digits. ...
    (comp.lang.java.programmer)
  • Re: How to calculate the median in a GROUP BY query -- Currency vs Double
    ... Currency is the most accurate numeric data type ... What about the DECIMAL data type? ... It can accommodate 28 digits before ...
    (microsoft.public.access.queries)
  • Currency and Number data type
    ... increase and am a bit confused whether Currency or Number ... mathematical calculations, except calculations that ... 15 digits to the left of the decimal point and 4 digits ... Number data type are shown to have a 7, ...
    (microsoft.public.access.queries)
  • Re: How to "POST" accounting data into underlying tables
    ... I'll switch the Data Type to ... I have a Receipts Allocation table in which the payment received for invoices ... caused by the use of the word "currency" in two ... Whether you select Currency or Number Data Type, amongst the Format ...
    (microsoft.public.access.macros)