How to Reset a Database Field to a Null Value in FoxPro

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, and 2.6
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, and 2.6
• Microsoft FoxPro for Macintosh, version 2.5b

SUMMARY

When a new database record is created in FoxPro, all database fields are set to a null value. You can test this by using the EMPTY() function on each field in a newly created record:

   USE customer
   APPEND BLANK
   ? EMPTY(cno)     & Returns .T.
   ? EMPTY(zip)     & Returns .T.

Sometimes you may want to reset a field to a null value after data has been entered in the field. For example, in a statistical program, you may want to edit a numeric score field so that it contains a null value for no answer and an answer of zero where the score was zero. See below for information on how to reset the various field types to null.

MORE INFORMATION

FoxPro Versions 2.0 and 2.5x

Character or Memo Fields:

To reset a character or memo field to a null value, replace the field with the null text string "":

   REPLACE fieldname WITH ""

To reset a date field to a null value, replace the date field with the null date string { / / }:

   REPLACE fieldname WITH {  /  /  }

A numeric field cannot be reset to a null value because the EMPTY() function returns .T. if a numeric field is blank or zero. To illustrate this behavior, issue the following command on an existing record when you are using a database that has date, numeric, and character fields:

   SCATTER MEMVAR BLANK
   GATHER MEMVAR FIELDS
   ? EMPTY(numericfield)
   BROWSE

To work around this limitation, do the following for numeric fields:

1. For each numeric field in the database, create a corresponding logical field.

2. Code the following logic into data entry/editing routines:

   If the numeric field contains 0 as data, set the logical field to .T. If the numeric field is to contain no value, set the logical field to .F.

3. Use the logical field as a flag in FOR clauses for subsequent operations, as follows

         AVERAGE response TO memvar FOR notempty
   

   where "response" is the numeric field and "notempty" is the corresponding logical field.

Logical fields also cannot be reset to null, since both a blank and a .F. logical field will result in EMPTY(<logicalfield>) returning a value of .T.

The technique used above for numeric fields can also be used for logical fields. When using this technique for logical fields, create a character field with a length of 1. This character field should contain T for true, F for False, or a space for empty.

NOTE: A character field containing only spaces will always return .T. for the EMPTY() function.

General Fields:

The contents of a general field can be deleted by issuing the command:

   APPEND GENERAL <generalfield>

FoxPro Version 2.6

A new command, BLANK, has been added in version 2.6. The BLANK command allows a field or fields to be reset to a true null or empty value.

1. To blank an entire record, issue the following command:

         BLANK
   

2. To blank selected fields, issue the following command:

         BLANK FIELDS <field1>, <field2>
   

Visual FoxPro 3.0

A .NULL value is available in Visual FoxPro. To set fields to null, replace the fields with .NULL.. To search for null values in fields use the ISNULL() function.

For more information, see the FoxPro 2.0 "Commands & Functions" manual or the FoxPro 2.5 "Language Reference" manual. For information about the ISBLANK() function and the BLANK command, refer to the FoxPro version 2.6 Help file.