ACC2000: Unexpected Results When You Use Int Function with Expression (242933)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q242933
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

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)

Note that the result returned is 5930, which is the expected result.

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)

Note that the result returned is 5929, which is unexpected. Because 59.3 * 100 is 5930, the expected result of the Int function is 5930.

REFERENCES

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

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

210423 ACC2000: Rounding Errors When You Use Floating-Point Numbers

Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbprb KB242933
©2004 Microsoft Corporation. All rights reserved.