Using required values, default values and NULL values in Access

When you create tables in Access you should be strict about what data your fields can contain, because the consistency of your data determines how well you'll be able to retrieve and group data later on. One important job in doing this is telling Access whether your fields require a value and what happens if no value is supplied.

Required values

If you have a Customer table in your database you want to be sure your customers get names, so you know what to call them when you send them a letter. And you might want to be sure you have their email addresses, so you can send them an email. If you want to be sure a field gets a value, you set the Required property to "Yes" in Access.

  1. Open your table in Design View (Right-click the table object and choose Design View in the menu)
  2. Select the field you want to make required.
  3. At the bottom of the screen, in the Field Properties panel, find the Required property and change its value to Yes.

    Required value in Access 

It's good to be strict and require a value when you know you are going to need it later. But don't overdo it. You wouldn't want to require a middle name for every customer, for example.

Default value

You can set a default value for Number, Text, Memo, Date, Hyperlink, Currency and Yes/No fields. When you set a default value for a field, Access will automatically enter this value when no value is supplied for the field. You can set a default value in the same place where you set the Required property.

Access default value

A default value is automatically entered by Access when no other value is supplied for the field.

When to use a default value?

You should use default values when a field must have a value, but you don't want to force the database user to provide a value. In a Customer table you might have a Yes/No field that indicates whether the user wants to receive your monthly newsletter. You could set the default value of 1 or "Yes" (without quotes).

Every new record than automatically gets a "Yes" value for the Newsletter field, unless something else (in this case "No") is provided.

default value in table

Access displays a Yes/No field as a checkbox.

About NULL values

A NULL value in databases is "no value". Required fields and default values are meant to prevent NULL values. If a field is not required and has no default value and no value is provided than the value of that field is NULL. Access won't actually show NULL in the places where there is a NULL value. Access will just show an empty cell. Databases like Microsoft Sql Server and Mysql do show NULL values explicitly. 

Null values in SQL server

SQL Server does show NULL values explicitly.

When to use a NULL/empty value?

The concept of a NULL value exists, because it is sometimes good to be able to say "I don't know this value".

Imagine for example a table in a fitness club application. The table registers the weight of the people who work out at the fitness club. Every member has his or her weight taken every week so the table holds the weight history of the club members. What would you insert if a member gets ill and misses his weekly weight registration? 0? 0 would be a bad idea, because 0 is a value. If you'd put in 0 when a person misses his or her weight registration and wanted to calculare this person's average weight over a year, the result would be influenced by the 0 values. This is a case where you would definitely need a NULL value.