Unwanted Numbers Rounding

[SE]

In my new Access Form, I typed 1007.69 and the number was rounded to 1008.00.
I changed the format in my table and form from standard to fixed, but
continued to get my number rounded. I then changed the decimal places from
auto to 2, but Access continues to round the number 1007.69 to 1008. How do I
stop this?

 

[Jeff Boyce]

Tables store data. Formats display data.

If your table has that field defined as an integer, you'll get whole
numbers, no matter how many decimal places your formatting displays...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Check the Field Size property of the field. The default Field Size for a
numeric field is Long Integer, and integers cannot hold decimal values. To
be able to handle decimal points, the Field Size needs to be Single, Double
or Decimal (or you can use the Currency data type)

 

[RoyVidar]

In my new Access Form, I typed 1007.69 and the number was rounded to
1008.00. I changed the format in my table and form from standard to
fixed, but continued to get my number rounded. I then changed the
decimal places from auto to 2, but Access continues to round the
number 1007.69 to 1008. How do I stop this?

But what is the data-type? (Field Size property)

If it is Integer or Long Integer, it means no decimals. Try Single or
Double as Field Size, which can contain decimals, or the data type
Currency.

 

[SE]

Many thanks to all who replied. I changed the field size to double and
everything is fine. It is interesting to note that as long as the field size
was long integer, not even the currency format stopped the rounding. Thanks
again.

SE

 

[Jeff Boyce]

NOTE: "double" data type stores numbers in a binary format, and is subject
to (binary) rounding errors. You might think you're storing 4.0, but
actually storing 3.99999999 instead!

If you will never need more than 4 decimal places accuracy, consider using
the Currency datatype instead...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Many thanks to all who replied. I changed the field size to double and
everything is fine. It is interesting to note that as long as the field size
was long integer, not even the currency format stopped the rounding. Thanks
again.

Don't confuse the Currency *FORMAT* with the Currency *DATATYPE*. A format
setting has no effect on what's stored in the field, just what's displayed. As
you have seen, setting a Long Integer's format to Currency leaves it a Long
Integer still, just puts a dollar sign in front of it.

The Currency Datatype is (somewhat oddly to my mind) not one of the subtypes
of Number; it's a separate datatype, just like Text or Date/Time. It's
actually stored as a huge scaled integer, with a range into the trillions and
exactly four decimal places, with no roundoff error. It can be displayed using
any numeric format, including the currency format, but it's just a number.