Tables > Data table formats > Numerics, NULLs

Empty field evaluations

An old time challenge handling dBASE data has been the evaluation of either non-filled fields or fields from which data have been deleted leaving them apparently empty.

Also, for numeric fields, depending on the application, non-filled fields may be evaluated equal to having the value of zero.

Generally, the dBASE table fields when created (as when inserting or appending records) contain NULL values.

If entering and storing some values in some record, later returning to that record and clearing individual fields, the "values" of those fields do not return to the native NULL "value" but rather to being just "empty".

In older versions of dBASE applications this may generate erroneous result when doing counting or statistics on either numeric or other fields.
E.g. entering some text in a field and later clearing this text, doing a COUNT on that column may include the cleared text field as containing a zero-length string.

Concerning Logical/Boolean fields this also raises a challenge as some dBASE compliant applications do not distinguish between FALSE and NULL.

With PdxEditor you won't see any of these problems.
However, as with any SQL implementation, care should be taken when counting string field values, as apparently empty cells may actually contain strings of invisible space characters. A pitfall safely avoided by using the SQL condition
WHERE TRIM(stringfield) <> ''

dBASE NULL values

or more strictly speaking, absent values, as NULL formally is not a value.
Absent values for numeric fields has always been a pain in dBASE, as the dBASE application traditionally did not distinguish between zeros and absent values.
This way there has been a tradition of carefully never relying on zeros as true values and often entering various numbers of pure 9-digits (9, 99, 999, 99.99 etc.) representing the positively entered missing values. However, many mistakes have been done not carefully avoiding including pre existing default zeros or the missing value 9s in counting and statistics.

But, properly programmed the dBASE table format actually does have a distinction between numerical absent values and zero values. This holds for all dBASE tables from level III, IV, 5 through level 7.

PdxEditor, NUMERICS and NULLs

PdxEditor neatly uses the allocated decimal numeric space according to the number of integers, and if exceeding the overall size, entering will be blocked.

This way PdxEditor allows entering all integers 1234567 to 123,456 into a NUMERIC(7,3) field, and entering 12345,678 would result in 12345,7, the trailing decimals being truncated after properly rounding of the last accepted decimal.
The PdxEditor never defaults numeric fields and have no problems distinguishing NULLs and zeros, however still respecting the possible default value setting in dBASE 7 table fields.

PdxEditor also distinguishes TRUE, FALSE and NULLs (empty) for LOGICAL fields.