Here is the test which is going to make you understand in two seconds the problem of amounts. Go and open the Debug Window and type:

? 100.99 - 50.45 - 50.54

VBA answers you:

-7,105427357601E-15

What happened? No, your processor did not make mistake in calculation. VB did not deceive. An equivalent code C ++ would give the same behavior. In fact, without saying it to you, VB used the type Single or Double for your amounts. This two type are representation in 'floating decimal point' of a real number. It is thus an estimate of the number which is recorded.

As the result of the test shows it, the estimate is very good (about 15 decimals), but will produce an error as soon if you want to compare one amount to another one.

For example, try the test:

? (100.99 - 50.45 - 50.54) = 0

returns False

And we have a real problem here. Without knowing it, your honorable application can produce this error!!

Fortunately, the designers of language and databases are aware of this problem. They thus prepared for you a special type: the Currency Type. This type is not a representation of 'floating decimal point' but a representation of 'fixed decimal point' with 4 decimals after the point. It is specially adapted to the monetary data because all the currencies of the world manage no more than 2 decimals in amounts. Thus with a precision of 4, it is good for other calculations. Besides, as this representation is fixed, the calculations of big numbers is particularly fast.

In your data bases: there is often a numeric(digital) fixed type for fields in data bases. But there is always as well a special type for amounts (Money with SQL-Server). It is more adapted.

Here we are, in conclusion it may be advisable to use systematically Currency (Access/VB) or Money (SQL-Server) to work on amounts.

This page was originally ported from the UtterAccess Forums. It is based heavily or in part on a post by Skrol29.
Currency: an underestimated Type (Money with SQL-Server)