ACC2000: Unexpected Results When You Use Int Function with Expression

• Microsoft Access 2000

SYMPTOMS

When you use the Int function that contains an expression, the result may be one number less than expected. For example, if an expression within the Int function should return 5930, the Int function may return 5929 instead.

CAUSE

At least one member of the expression within the Int() function is a double precision number and contains a decimal fraction.

RESOLUTION

Wrap the CDec function around each member of the expression that could potentially contain decimal fractions. For an example of how to do so, follow these steps:

1. Open the sample database Northwind.mdb.



2. Press ALT+F11 to open the Immediate window in the Visual Basic Editor.



3. Type the following expression in the Immediate window, and then press ENTER:




?Int(CDec(59.3) * 100)

MORE INFORMATION

By default, Visual Basic for Applications uses the Double data type for each member of an expression contained within an Int function. The Double data type is a floating-point data type that is used to store both integer and decimal values. The problem is that the computer must store the value in binary, rather than in decimal format. However, not all decimal fractions have exact binary equivalents; therefore, the value interpreted by the computer is going to slightly less or slightly more than the actual value.

For numbers whose binary equivalents are slightly larger than the actual value, the Int function returns the expected result. For numbers whose binary equivalents are slightly less than the actual value, the Int function returns one number less than the expected result.

In the example shown in the Steps to Reproduce Behavior section later in this article, the value 59.3 causes the problem (the "100" in the expression is a whole number, and therefore is exempt from the problem). The computer is not using the actual value 59.3 in the calculation, but rather its closest binary equivalent. The closest binary equivalent to 59.3 is 59.29999999999999, which is slightly less than the actual value. This means that the expression inside the Int function actually being computed is (59.29999999999999 * 100). This expression results in the number 5929.999999999999, which is slightly smaller than 5930. Because the Int function truncates the number instead of rounding it, the result of Int(5929.999999999999) is 5929.

By wrapping each member of the expression in the CDec function, you are converting it from a Double to a Decimal data type. Decimal data types are stored as scaled integers, and therefore are exempt from the rounding errors associated with floating-point data types.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.



2. Press CTRL+G to open the Immediate window in the Visual Basic Editor.



3. Type the following expression in the Immediate window, and then press ENTER:




?Int(59.3 * 100)

REFERENCES

For additional information about rounding errors associated with floating point data types, click the article number below to view the article in the Microsoft Knowledge Base:

Q42980 (Complete) Tutorial to Understand IEEE Floating-Point Errors

Additional query words: prb wrong not correct off by incorrect

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb