Require a value in a field

There are two basic ways that you can require a value in a field:

• Set the field's Required property to Yes    You can do this by opening the table in Design view. This method is easy and a good choice if you only want to change one field one time.

• Use a data-definition query that creates a non-null index on the field    You can do this by using SQL view. This method is not as easy as using Design view, but has an advantage: you can save the data-definition query and use it again later. This is useful if you periodically delete and re-create tables and want to require values for some of the fields.

What do you want to do?

Set the Required property for a field to Yes

Create a non-null index for a field by using a data-definition query

Set the Required property for a field to Yes

1. In the Navigation Pane, right-click the table and then click Design View.

2. Select the field that you want to require always has a value.

3. In the Field Properties pane, on the General tab, set the Required property to Yes.

   

Note: If you set the Required property to Yes for a field in a table that already contains records, Access gives you the option of checking whether the field has a value in all existing records. Regardless of whether you accept this option, new records will require a value for the field.

Top of Page

Create a non-null index for a field by using a data-definition query

1. On the Create tab, in the Other group, click Query Design.

2. On the Design tab, in the Results group, click the arrow under View, and then click SQL View.

3. Delete all the SQL from the query.

4. Type or paste the following SQL into the query:

   CREATE INDEX index_name
    ON table (field) WITH DISALLOW NULL
   

5. In the SQL, replace the variables as follows:

   • Replace index_name with a name for your index. It is a good idea to use a name that helps you determine what the index is for. For example, if the index is to make sure that social security numbers exist for every record, you might name it require_SSN.

   • Replace table with the name of the table that contains the field to be indexed. If the table name has spaces or special characters, you must enclose the name in square brackets.

   • Replace field with the name of the field to be indexed. If the field name has spaces or special characters, you must enclose the name in square brackets.

6. Save and close the query.

7. Run the query to create the index. Note that you can run the query from a macro by using the RunSQL macro action. For more information, see the See Also section.

Top of Page