Type one number into Access, get another in the database

[Maury Markowitz]

I have a form in an ADP bound to a float field in our SQL Server. The
user types in something like 1.23, and often this ends up in the
database as 1.22999999...

At first I believed this was due to the way that SQL Server stores
floats, but now I'm not so sure. When I open that table in the
Database View and "fix" the number, sure enough, it stores it
correctly and works perfectly from then on. Moreover, when I re-create
the VBA code that saves out the field in SQL, the number is correctly
stored as 1.23.

It appears that the problem occurs when Access's (or VBA/COMs) float
data type is converted into SQL it gets changed somehow. Can anyone
shed some light on this?

Maury

 

[Tom van Stiphout]

On Mon, 11 May 2009 08:55:29 -0700 (PDT), Maury Markowitz

This is a FAQ; you should have no problem finding information using
your favorite search engine.
In short: many floating point values cannot be stored accurately, and
roundoff is a normal side effect. Typically you will round the number
to fewer digits before use, so it really doesn't matter whether the
value is 1.23 or 1.22999999999 or 1.23000000001

-Tom.
Microsoft Access MVP

 

[Maury Markowitz]

In short: many floating point values cannot be stored accurately

Then how is it that typing the same number into the database directly,
using UPDATE or even editing the table view, stores the proper number?
If it can't store that number that's one thing, but it can.

Maury