0

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

Catarina Ferreira
  • 1,824
  • 5
  • 17
  • 26
Tian Li
  • 11
  • When you say "decimal" numbers, what is the precise data type of the columns? For a linked Excel sheet, the type is probably a 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. – C Perkins Jun 19 '17 at 22:59
  • Also as a hint to writing better SO questions: If you show the result of some calculation then always show what numbers you used to get that calculation, not just the result. Also, instead of an image, take the time to compile a table of actual sample values that are visible in the question and can be copied and pasted. – C Perkins Jun 19 '17 at 23:05

2 Answers2

1

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.
C Perkins
  • 3,733
  • 4
  • 23
  • 37
1

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.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • +1 I'm assuming by "amounts" you mean "monetary amounts", since Currency lacks enough decimal precision (only 4 digits) to represent any general amount. – C Perkins Jun 20 '17 at 23:44