numeric overflow in vfp

Question

hi all,

Is there any function to check a numeric value of a table field type can be validated?

i.e. If the table field is N(20,0), we can't input 99999999999999999999 to the table.

Numeric overflow error displays. How to validate user input?

regards.

Answer 1

Numeric field allows the size 20 digits but FoxPro cannot handle numbers in such a big accuracy. FoxPro internally works with cca 14 digits in double precision format or a little bit more in Currency fields which are simulated as 8 byte integer numbers. Your number is rounded to 1e+20 which is handled as "numeric overflow" in some situations, e.g. in BROWSE window.

You should not use Numeric fields for numbers of this accuracy and you have to restrict the entry by appropriate Input Mask. If you really need 20 or more digits then you have to use different data type (a character string is available only) or some external library.

Answer 2

Help topic about the double field type suggests it has more accurracy than numeric.

"When you need more accuracy, for example, when you have a fixed amount of storage in your table or true floating-point values, use the Double data type instead of Numeric."

The reason behind it is, internally, in memory, all numeric types are handled as double, there is even no int variable in foxpr, you can only set variable = CAST(n as I), but even that does not remain an int, if you work on it.

In regard to the double floating point type, you can expect 52 bits precision or the mantisse (see http://en.wikipedia.org/wiki/IEEE_754-2008), which translates from binary to decimal as 15.95 decimal places, so 15 are guaranteed, the sixteenth is mostly also accurate.

As a floating point is composed of a value 1 dot mantisse * 2^exponent it does not mean 15.95 decimal places after the decimal point, but about 16 decimal places from the highest significant digit, so actually while the N(20) field is possible, you can only store N(16) with exact precision and N(17) with exact precision, if the most significant leftmost digit is always lower 2, due to the nature of the mantisse, there always is the transition from decimal to binary values, the precision is 52 binary digits.

Actually, if you open up the dbf with an N(20) field you can see the number stored as a string, really, not as a binary numeric format, so theoretically you can use the full 20 places precision, but the nature of handling numeric values of foxpro in memory different than in a table field is in your way.

Do you really need N(20), for what? Some serial number? Rather use string for such things, a C(20) field has no such problem.

Bye, Olaf.

PS: And yes, currrency is a little more precise.

8 Bytes, range - $922337203685477.5807 to $922337203685477.5807

Fixed 4 decimal places, you can use the range of $0.0000 to $99999999999999.9999 to store any N(18) value precise in 8 instead of 20 bytes.

Answer 3

If you need N(20) then see the other replies.  If that happened to be just an example and you are running into the problem with fields of a smaller size you can check for the error condition with something like

IF newValue >= 10 ^ FSIZE('fieldname')
     *** Numeric Overflow
ELSE
     *** Valid value
ENDIF