Access Required and Allow Zero Length properties
In Access you can indicate whether a field is a required field or not. When a field is required Access requires a value for that field when a new record is entered into the table. If no value is provided, Access will not insert the record and show an error message. The Allow Zero Length property is not the same as the Required property, but they are related. In this article I will show you how to set Required and Allow Zero Length constraints and I will explain what these properties mean.
Any field in Acces can be a required field. By default, fields in Access are not required. Here is how you set a field to required.
- Open your table in Design View (right-click the name of your table and choose Design View from the menu)
- Select the field that you want to set to Required.
- Look at the field properties at the bottom of the screen, find
the Required property and change its value to
Required fields require a value when you enter a new record. If you try to insert a record that doesn't have a value for a requiref field, Access will not insert the record and it will show you an error message.
Now, that was pretty simple wasn't it? Let's now look into what the Zero Length value is all about.
String fields and Zero Length values
In database and programming lingo, a string means a string of characters, or, "text". In Access the Text, Memo and Hyperlink fields are all string fields. They contain pieces of text.
Also in database and programming lingo, when you provide no value we say you are providing a NULL value. The term NULL value is a bit confusing, because what it means is NO value.
Now, when you set a field to Required in Access you are saying you can't provide a NULL value for this field. That is, you can't provide no value. If you do, Access will complain, like you saw in the previous paragraph.
A Zero Length value is an "empty string". It is also empty, because it is a string of length zero.
The difference between NULL and a Zero Length string is that NULL is no value and a Zero Length string is a value.
The difference between NULL and Zero Length in Access is mostly conceptual, but it becomes more important when you are going to write queries.
In SQL, if you would want to select all customers with aZero Length first name you would write the following query.
SELECT * FROM Customers WHERE FirstName = ""
If you would want to select all Customers with a NULL first name you would write. If Allow Zero Length values is set to No, you could be sure that all customers without a first name would be selected with this query.
SELECT * FROM Customers WHERE FirstName = NULL
If you would set the FirstName field to required and you would allow Zero Length values, you would have to write the following query to be sure that all customers without a first name are returned.
SELECT * FROM Customers WHERE FirstName = Null Or FirstName = ""