访问“不正确"比较浮点数的操作. [英] Access comparing floating-point numbers "incorrectly"

问题描述

我正在Windows 7 64上使用MSAccess 2010.

I am using MSAccess 2010 on Windows 7 64.

我正在比较查询中的两个表.我要加入一个复合PK并选择其中表A列A<>表B列A所在的行，其中两个表的A列都为double.

I am comparing two tables in a query. I am joining on a composite PK and selecting rows where table A column A <> table B column A where column A for both tables is a double.

它可以工作120行.对于28行，表A列A的值为0.088 <>表B列A的0.088.

For 120 rows it works. For 28 rows a value of 0.088 in table A column A <> 0.088 in table B column A.

在任何地方都没有使用文本函数.

There are no text functions used ANYWHERE.

当我向查询中添加列时:[TableA]！[ColumnA]-[TableB]！[ColumnA]我返回的值类似于-1.38777878078145E-17.

When I add a column to the query a:[TableA]![ColumnA] - [TableB]![ColumnA] I return values like -1.38777878078145E-17.

当我将查询导出到Excel并进行数学运算时，结果= 0.

When I export the query to Excel and do the math the result = 0.

0.088-0.088<> 0怎么可能? 0.088 double<> 0.088 double是怎么可能的?

How is it possible that 0.088 - 0.088 <> 0? How is it possible that 0.088 double <> 0.088 double?

推荐答案

这是一个非常普遍的问题，与任何计算上下文(不仅仅是Access)中的浮点数的性质有关.看起来像是一个悖论，但事实是Double值可以近似表示非常广泛的数字范围(最多15个有效数字，参考:

This is a very common problem related to the nature of floating-point numbers in any computing context (not just Access). It seems like a paradox, but the fact is that a Double value can approximately represent a huge range of numbers very precisely (up to 15 significant digits, ref: here), but that representation is almost never exact. That's why:

• 精确比较浮点数(例如x=y)有时可能会失败，因此

• exact comparisons of floating numbers (e.g. x=y) can sometimes fail, and therefore

您永远不应依赖两个Double字段之间的JOIN.

you should never rely on a JOIN between two Double fields.

有关浮点数的更多信息，请此处. Wikipedia还可以在此处解决该问题.

More information on floating-point numbers is available here. Wikipedia also addresses the issue here.

如果您的字段的小数位数需要精确比较，那么您可能需要考虑将其更改为Currency或Decimal.

If you have fields with decimal places that require exact comparisons then you may want to consider changing them to Currency or Decimal.

这篇关于访问“不正确"比较浮点数的操作.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！