Decimal Precision in VBA

[billelev]

I have the following field in a query:

Quantity: Sum([qrytransactions].[Quantity]*[StockMultiplier])

Where [Quantity] is a decimal, such as 100 or 100.23 etc. and [StockMultiplier] is either -1 or +1.

In the source tables, both fields are defined as type double.

The Sum query works fine, unless the sum should be equal to zero. In these cases, I get some very obscure rounding errors. For example, given the following data:

Quantity, StockMultiplier
100, 1
0.118, 1
0.112, 1
100.23, -1

I get the following result in the query: -1.4210854715202E-14 (rather than 0)

Can anyone help with this?

I tried writing a function to "force" the result I wanted. If I express the return value as Currency I see the query result as required (but if it is double the problem persists). This seems far from ideal though.

Expr1: Sum(CalculateQuantity([qrytransactions].[Quantity],[StockMultiplier]))

Public Function CalculateQuantity(qty As Double, multiple As Double) As Currency
 
    If multiple = -1 Then
        CalculateQuantity = -qty
    Else
        CalculateQuantity = qty
    End If
 
End Function

[youmike]

You've touched on a large & complex subject and there are very probably long explanations of the causes. One is that currency variables have four decimal place precision, as I recall .

A possible fix, if you know how many decimal places you want in your result, is to multiply your numbers by that number, and take the integer of the results to a series of long integer variables, add them add them all up and divide the sum by the number of decimal places.

If the mutiplier is always an integer, it would be better to define it as integer.

You could modify this general approach to fit your needs more precisely. Post again if this is not clear.

[Stewart Ross]

Hi. Just to add to YouMike's reply, it should be borne in mind that ALL floating-point arithmetic is approximate to some degree - small rounding errors of the order of 10^-14 occur with Excel just as with Access, for example. This is because it is simpy not possible to represent most values so exactly that there will never be rounding errors - and for some values (1/3 being one of many) there are no exact representations in decimal form in any event.

If you are comparing floating point values to 0 you will always run into rounding problems. The norm in these circumstances is to compare the value to a near-zero value below the smallest value you would count as zero instead. Using currency values as an example, you can compare the absolute value of the result to 0.0001, for instance, instead of to 0 - this is well below the lowest currency value you would normally encounter, yet well above the 'noise threshold' of around 10^-11 which rounding errors typically involve.

-Stewart

PS many years ago software for banking etc used binary-coded decimal (BCD)representations of each digit in a number to be able to represent that number exactly, which floating-point numbers cannot do. However, calculations themselves can still result in inexact values even with BCD used (divisions, for instance, taking the case of 1 / 3 again). I don't know if BCD is still used these days.

[billelev]

Thanks for your answers, both of you. It seems that the easiest solution is to compare to a nearly non-zero number. Knowing that 10^-11 is the threshold is very useful.

[NeoPa]

The Data Type Currency and the Field Size Decimal are provided for exactly that reason. I believe they actually use a form of BCD but the help description is not greatly helpful there I'm afraid.

What is special about BCD is that the underlying data is stored and worked out using decimal rather than converting to binary and then back for display.

This means that rounding errors (from binary to decimal) are avoided.

While some fractions will always be held inaccurately in decimal, decimal numbers themselves (of which currency values are some) will always be handled accurately.

In short, this is what you, and everybody else, SHOULD be using for any values where arithmetic rounding errors are an issue.

I'm a hypocrite of course, as I still use the defaults ones in most cases ;)

[ADezii]

Thanks for your answers, both of you. It seems that the easiest solution is to compare to a nearly non-zero number. Knowing that 10^-11 is the threshold is very useful.

I think NeoPa has a very valid point in Post #5 in that you can use the Decimal Data Type to avoid the problem of rounding errors. Remember that Decimal is not a Data Type within itself, but a SubType of Variant. You cannot simply declare a Variable as Type Decimal, no such animal exists. You must create a Variant whose SubType is Decimal using the CDec() Function.

[youmike]

An important point regarding Currency fields is that they have an underlying precision of of four decimal places. This can bring its own rounding problems if you need to multiply a currency value with a decimal quantity to get a total value.

If you are working in a situation where VAT or sales tax need to be calculated as a percentage of value, this is another area where care is needed. Here in South Africa, we have 14% VAT and I have used rounding via Int(CalcValue*100 + 0.5)/100 as a predictable means of calculation.