dBASE III\IV numeric field TYPE N\F


dBASE III+ uses the IEEE standard floating point math internally for numerical data and numeric computations. "Float" types are not always accurate in their decimal portion, since you cannot represent the infinite set of real numbers with a finite set of binary digits. With Float you get 15 places accuracy, and a 90% accuracy on the 16th place. Problems arise when the float number is only 2 decimals wide and you are doing math on this number. The number is very inaccurate in its last digit. IEEE has inherent inaccuracies even to two decimals with any operation that rounds out the number. When working with money figures, people usually want to limit their fields to the exact number of digits that are required. Conventionally one does not use 5 place accuracy on dollars and cents. Using 2 digits places the inaccuracy on the last digit of the type F number. When math is performed on these fields you can be a few pennies off. As long as you are aware of the potential problems, you can code around them. See -> Potential III PLUS problems with ROUND(), below.

Because of the potential rounding problems with IEEE, dBASE IV type NUMERIC uses BCD ( Binary coded decimal ) operations for numeric data and numeric computations. BCD math is not floating point oriented and is MORE ACCURATE WHEN A FIXED NUMBER OF DECIMALS ARE SPECIFIED. With dBASE IV type N you get adjustable accuracy of 10 to 20 digits. In dBASE IV type N is accurate to the restricted number of digits assigned, as per the database structure.

In dBASE IV, IEEE operations are still supported using the new FLOAT data type. Internally dBASE IV uses IEEE if any of the parts of the equation are Float.

Float numbers are good for scientific calculations because you may be using multiple numbers that have widely different numbers of decimals, such as when performing repeated multiplication, division or with trigonometric functions. Float numbers will return the MOST ACCURATE DECIMAL REPRESENTATION WHEN THE DECIMAL PLACES ARE NOT OF A FIXED LENGTH. This type is also slightly faster in processing than an equal "Numeric" value, and will be processed by a math co-processor, if you happen to have one.

If using dBASE IV, in financial and accounting operations, use NUMERIC type. Use FLOAT only where the accuracy at the last decimal point is not crucial to the calculation.

Potential III PLUS problems with ROUND()

?VERSION(0) && x77, x95c, x100, x102 

(1) The method used by dBASE III PLUS to store numbers internally causes some unexpected results when operations are performed on numbers. Due to problems in the method used, comparing and rounding of numbers may result in inaccurate results as demonstrated below.

Numeric Compare

dBASE III PLUS handles numbers internally in floating point representation. However, numbers are entered and displayed in fixed decimal representation. In order to operate on a number, dBASE III PLUS must first convert it from fixed decimal to floating point. To display the number after the operation, it must be converted back to fixed decimal. The errors that appear in numeric compares are a result of these conversions.

Beware of all comparisons involving numeric expressions. For example

? INT(4.1-2.1) = 2 

In this example, the result of the compare is correct. The result of the INT() is in error, as shown below.

? INT(4.1-2.1)
mem = .0003 * 10000 
? INT(mem) 
mem = .0006 * 10000 
? INT(mem) 5 

To insure accuracy, use the VAL() function in conjunction with STR(). Anytime numeric accuracy is necessary, use the following construction.

? VAL(STR(4.1-2.1,3,1)) 2 

(2) ROUND()

The ROUND() function does not round a five unless it is followed by a digit other than zero. For example,

? ROUND(.145,2) 
? ROUND(.1450,2) 
? ROUND(.1451,2) 

Commands and functions which may force dBASE III PLUS to round numbers are:


STR() The dBASE III PLUS manuals state (page U6-55 [Orig.] and U6-77 [Rev. 6/86]) that if fewer decimals are specified than are in a numeric expression, the results are rounded to the number of decimals specified. However, like the ROUND() function, the STR() function does not properly round the last digit of a number if it is a five. For example,

mem = 4.55 
? STR(mem,4,1) 
* This should be a 6. 
? ROUND(mem,4) 
mem = 4.555 
? STR(mem,4,1) 
* STR() rounds properly if it is not extended 
* to the last digit in the number. 

TRANSFORM() TRANSFORM() automatically rounds a number according to the number of decimal places specified by SET DECIMALS TO. For example

mem = 12.124 
? TRANSFORM(mem,'99.999') 
* displays 12.120 

TRANSFORM(), like ROUND(), does not round a five unless it is followed by a non-zero digit.

[Home] [FAQ Index]