Incorrect Maths 1

[SteveCop2]

Access is giving me strange results for perfectly straightforward calculations. For example, try the following in the Immediate window of VBA:
1108.91 - 306.38 - 802.53
The answer should be 0 (or 0.00) but is actually 1.13686837721616E-13 (which in non-scientific display is 0.000000000000114.

If the calculation is performed in two stages it is OK:
1108.91 - 306.38 = 802.53
802.53 - 802.53 = 0.00.

So what is going on please? More importantly, how do I stop it happening? I maintain a finance processing system and accuracy is slightly important!

 

[Remou]

As far as I recall, this is a type conversion problem. Try:

?ccur(1108.91) - ccur(306.38) - ccur(802.53)

 

[SteveCop2]

A little further information:

The problem comes about when there is a chain calculation requiring more than one negative number to be deducted and where both the negatives have decimal places. Even then it is not always the case that you will get a non-zero result but when it does happen it is always repeatable. One solution (very messy) would be:

VAL(FORMAT(number 1 - Number 2 - Number 3,"Fixed"))
instead of number 1 - number 2 - number 3. It works but surely this is not how we should have to structure calculations?!!

 

[Remou]

I am not sure I follow your second post. I think you will find that if you assign a suitable type to each number before performing the calculation, you will get the correct answer.

 

[SteveCop2]

Thanks, Remou! That's a lot more stylish than I was suggesting.

I remember having this problem many years ago and it was something to do with how a computer does its maths down at bit level (ie everything is an addition of some sort, even a subtraction!) which gives rise to these tiny figures. Insignificant though they may be, they still stop zero being zero and that's a bit of an issue. I suppose the CCUR function does not work to such very fine figures and nor does CSNG which I also see gives the correct result. Not very satisfactory really when Access struggles to take a couple of numbers from another and can't do it properly! Still, that's progress!!

 

[irishjoe]

I have had loads of problems of getting really long numbers when they should be nice and short. In general, I let access store the numbers however it likes and then when I display them on forms or in queries that are being exported, I use the round function.

 

[JoeAtWork]

>> In general, I let access store the numbers however it likes

Instead of letting Access make arbitrary (and often inaccurate decisions), why not use explicite data types that round correctly (like Currency, for example)?