ACC2000: Null Value Does Not Pass Validation Rule (209021)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209021
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

If a field has a validation rule and you leave the field blank (or Null) in a table's Datasheet view, you may be unable to save the record.

CAUSE

This behavior occurs when you add a new record with two fields, one of which has a data type of AutoNumber. The record cannot be saved until it has received some input.

RESOLUTION

Include "Or Is Null" as part of the validation rule for the field. For example, a validation rule for a text field can be:

"A" Or "B" Or Is Null

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create the following new table and save it as Table1: 
       Table: Table1
   ---------------------------------------------------
   Field Name: ID
      Data Type: AutoNumber
   Field Name: Code
      Data Type: Text
      Validation Rule: "A" Or "B" Or Is Null
  2. View the table in Datasheet view.
  3. Select the Code field. Type A and press ENTER.

    Note that you can move to the next record.
  4. In the next record, press TAB to move to the third record.

    Note that you cannot move to the next record because the Code field cannot initially be Null. The only user-input field in this table must receive some data before the record can be accepted.
  5. Select the Code field in the second record. Type A and press ENTER. Delete the "A" from the field, and press TAB.

    Note that you can move to the next record now because the record was initially saved with data.

REFERENCES

For more information about the ValidationRule property, click Microsoft Access Help on the Help menu, type validationrule, validationtext properties in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb KB209021
©2004 Microsoft Corporation. All rights reserved.