CODEWITHSUNDEEP
ms-access
Tian Li
Tian Li

Reputation: 11

Microsoft Access Automatically Increase Decimal

Hi My Fellow Access user. I am using Access to do reconciliation, by link two Excel sheets. the number are two decimal. Linked Table view

However, when i was trying to run subtraction between two numbers both 2 decimals, it return results like this:

enter image description here

Appreciate if anyone know how this could happen, and what steps I need to take to fix it?

Thanks Tian

Upvotes: 0

Views: 319

Answers (2)

C Perkins
C Perkins

Reputation: 3884

For a linked Excel sheet, the column type is probably Double, a 64-bit floating point number. This problem you experience is probably due to an inherent limitation of floating point numbers and is not unique to Excel or Access.

This Stack Overflow question asks essentially the same thing: Why does this subtraction not equal zero?

Excel is no exception, only that the default formatting might not show the necessary precision to reveal the behavior. Selecting scientific format or increasing the number of displayed decimal places will reveal the same behavior.

Consider the following:

  1. For monetary amounts, convert values to Currency using the CCur() function. Currency is a fixed-decimal value, but be aware it only has 4 digits to the right of the decimal. (Updated to reflect advice from Gustav)
  2. Convert values to fixed-point Decimal type using CDec() function before performing the math. There is no native VBA Decimal type, so these are variants containing Decimal values. But upon conversion back to floating-point, it is still possible to experience extra digits.
  3. Round the results using the Round() function, but again this is not guaranteed to eliminate floating-point limitations.
  4. Choose an explicit format for displaying the numbers.

Upvotes: 1

Gustav
Gustav

Reputation: 55906

Don't use the linked Excel data directly.

Create simple select queries where you can convert and trim your data. Like:

Select SomeField, Description, CCur([TotalAmount]) As Total
From YourLinkedExcelTable

When dealing with amounts, always use Currency as data type.

Now, calculate your Diff using the query.

Upvotes: 1

Related Questions