Content
Resources
To Do
Toolbox

 Currency: an underestimated Type (Money with Sql-Server)
Currency: an underestimated Type (Money with Sql-Server)

This example should help you understand the problem of calculating amounts with Single or Double data types.

Open the Debug Window and type:

? 100.99 - 50.45 - 50.54

VBA returns:

-7,105427357601E-15 or -7.105427357601E-15

It depends on whether your regional settings specify the , or the . for the delimiter.

In any event, the result is displayed in scientific notation, indicating a very small number, with up to 15 decimal places in it, although you were probably expecting that the result would be 0.

What happened? No, your processor did not make a mistake. VBA did not deceive you. An equivalent calculation in C ++ would have the same behavior. In fact, without telling you, VBA used the Single or Double datatype. These two datatypes are represented in 'floating decimal point'. The calculation is thus an estimate of the number which is calculated.

The result shows the floating point estimate is very good (about 15 decimals), but will produce an error if you want to compare one amount to another one.

For example:

? (100.99 - 50.45 - 50.54) = 0

returns False

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

Fortunately, the designers of coding languages and databases are aware of this problem. They created the Currency data 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 monetary data because all currencies of the world manage no more than 2 decimals in amounts. Moreover, with a precision of 4, Currency data type is good for other calculations as well. In addition, as this representation is fixed, 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 also a special type for amounts (Money with SQL-Server).

In conclusion it may be advisable to use Currency (Access/VB) or Money (SQL-Server) with amounts where you need to be sure floating point calculations won't cause unexpected results.

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)