scientific notation conversion question

  • Thread starter Thread starter Lotto
  • Start date Start date
L

Lotto

I am trying to do a simple subtraction in a table that has 4 digits
beyond the decimal. I can't get the equation to not use scientific
notation in some of my result fields. See example below.


0.345-0.351=-6.00000000000001E-03


Can anyone help get these numbers converted?
 
I am trying to do a simple subtraction in a table that has 4 digits
beyond the decimal. I can't get the equation to not use scientific
notation in some of my result fields. See example below.


0.345-0.351=-6.00000000000001E-03


Can anyone help get these numbers converted?
Click to expand...

Not if you insist on using a Double Float datatype, I fear! Double numbers are
approximations, with about 14 decimals (48 bits) of precision; just as you
cannot depict the number 1/7 exactly as a decimal (0.142856142856142856...) so
you cannot depict 0.006 exactly as a binary, hence the "roundoff error".

The solution - if you are satisfied with exactly four, no more, no fewer
decimals - is not to use a Number... Double, but instead to use a Currency
datatype. This is a scaled huge integer with exactly four decimals and no
roundoff. Alternatively, you can use a Decimal datatype and specify your own
desired precision and scale.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Not if you insist on using a Double Float datatype, I fear! Double numbers are
approximations, with about 14 decimals (48 bits) of precision; just as you
cannot depict the number 1/7 exactly as a decimal (0.142856142856142856....) so
you cannot depict 0.006 exactly as a binary, hence the "roundoff error".

The solution - if you are satisfied with exactly four, no more, no fewer
decimals - is not to use a Number... Double, but instead to use a Currency
datatype. This is a scaled huge integer with exactly four decimals and no
roundoff. Alternatively, you can use a Decimal datatype and specify your own
desired precision and scale.
--

             John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:http://social.msdn.microsoft.com/Fo...al.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com
Click to expand...

When I tried to make currency, it only went to 2 digits behind the
decimal, I truly do need four. I worked around this as I only wanted
to know if there was a difference between the numbers. Used a true/
false to compare the two numbers, as I don't need the calculated
number. Triggers a customer letter, differnent one for the changed
rates, and one to state that there is no change. Thanks for your
help- Laurie
 
When I tried to make currency, it only went to 2 digits behind the
decimal, I truly do need four.
Click to expand...

It actually stores four - it only SHOWS two. Try creating a Currency field;
set its Format property to

"#.0000"

and/or its Decimal Places property to 4. You'll see that it does in fact store
four.
I worked around this as I only wanted
to know if there was a difference between the numbers. Used a true/
false to compare the two numbers, as I don't need the calculated
number.
Click to expand...

But you DO need the calculation to be correct! Subtracting two (nominally
equal) Doubles can and will (sometimes) give a difference such as 3.2E-14 or
-1.9E-15, which is nonzero and would trigger a letter that you REALLY don't
want to send ("Please pay your balance due of $0.00 immediately or we will be
obliged to take legal action...", and yes, I once got such a letter).

Please retry the Currency. It DOES work.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top