Puzzling Formula Result (1 Viewer)

[liamrowan]

I have a qry that is based on table. The qry subtracts one field from another to produce a result:

Here are the actual numbers:

Field 1: 185,013.15
Field 2: 185,013.15

The result (returned in Field 3) is:

2.91038304567337E-11

I don't know why the result isn't zero? There are a lot of other rows in which Fields 1 and 2 are equal and the result returned in Field 3 IS zero as expected. I can't figure out why some of the rows are producing the above result.

I compacted and repaired the datase, and I tried exporting the table and qry to a new database just to see if perhaps the database had become corrupted, but the result was the same.

Ideas?

Thx, Wm

 

[pbaldy]

My guess would be that the data type of one or both fields is Single or Double, which are floating point numbers. That means 185,013.15 might actually be stored as 185,013.1500000001 or 185,013.1499999999. If you only need the two digits of precision, I'd change the data type to Currency or something, or you can force the issue in your query:

CCur(Field 1) - CCur(Field 2)

 

[jdraw]

Have you tried using Currency datatype?

OOPs I see Paul was answering while I was typing..

 

[NickHa]

How are the values of fields populated? If they are the result of calculations, then the differences may be due to them. The fields as displayed show the precision of the format, which will round up/down.
You could try rounding the numbers before the subtraction, but it really depends on how the data are calculated in the first place.

 

[liamrowan]

Thanks, everyone. The change to currency fixed the issue. Wm

 

[gemma-the-husky]

as a general point, manipulating real numbers is often complicated by imperfect precision. comparing any real number to a specfic value, or another variable may unaccountably fail. the best solution is generally to compare the absolute difference to a delta

eg
if abs(real1 - real2)<0.00000001 .... then treat as equality



for example, a simple number like 0.1 just cannot be represented as a binary number.