.You can control the way that a blank field is handled by setting different combinations of the field's Required and AllowZeroLength properties. The AllowZeroLength property is only available for Text, Memo, or Hyperlink fields. The Required property determines whether an entry must be made. If the AllowZeroLength property is set to Yes, then Microsoft Access will differentiate between two kinds of blank values: Null values and zero-length string values.
This topic provides reference information about:
The difference between Null values and zero-length strings
When to allow Null values in a field
When to allow neither Null values nor zero-length strings in a field
When to allow both Null values and zero-length strings in a field
When to allow only zero-length strings or some value in a field
Distinguishing Null values from zero-length strings
Finding zero-length strings or Null values
Microsoft Access allows you to distinguish between two kinds of blank values. In some situations, a field may be left blank because the information may exist but is not currently known, or because the field doesn't apply to the record at all. For example, if a table has a Number field, you can leave the field blank if you're unsure of the customer's fax number, or whether the customer even has a fax. In this case, leaving the field blank enters a Null value, which means "I don't know." Entering a zero-length string by typing double quotation marks (" ") means "I know that there is no value."
Return to top
If you want to leave a field blank and don't need to determine why the field is left blank, set both the Required and AllowZeroLength properties to No. This is the default when creating a new Text, Memo, or Hyperlink field.
Return to top
If you never want a field to be left blank, set the Required property to Yes and the AllowZeroLength property to No.
Return to top
If you want to be able to distinguish between a field that is blank because the information is unknown and a field that is blank because it doesn't apply, set the Required property to No and the AllowZeroLength property to Yes.
In this case, when adding a record, you would leave the field blank (which enters a Null value) if the information is unknown. On the other hand, you would type double quotation marks (" ") with no space between them to enter a zero-length string, indicating that the field doesn't apply to the current record.
Return to top
If you only want a field left blank if you know that a field isn't applicable to a record, set both the Required property and the AllowZeroLength property to Yes.
In this case, the only way to leave a field blank is to type double quotation marks with no space between them, or press the SPACEBAR to enter a zero-length string.
The following table shows the results of all possible settings of the Required and AllowZeroLength properties.
| Required | AllowZeroLength | User's action | Value entered |
|---|---|---|---|
| No | No | Presses ENTER | <Null> |
| Presses SPACEBAR | <Null> | ||
| Types " " | (not allowed) | ||
| Yes | No | Presses ENTER | (not allowed) |
| Presses SPACEBAR | (not allowed) | ||
| Types " " | (not allowed) | ||
| No | Yes | Presses ENTER | <Null> |
| Presses SPACEBAR | <Null> | ||
| Types " " | <Zero-length string> | ||
| Yes | Yes | Presses ENTER | (not allowed) |
| Presses SPACEBAR | <Zero-length string> | ||
| Types " " | <Zero-length string> |
Return to top
When you view data in a field that contains both Null values and zero-length strings, the fields look the same — they contain no values. You can distinguish Null values from zero-length strings by using an expression in a query field or in the control source of an unbound control in a form, a report or a data access page. The following expression returns “Unknown” if the field contains Null and “ZLS” if the field contains a zero-length string; otherwise, the expression returns the value in that field.
=IIf(IsNull([fieldname]),”Unknown”,Format([fieldname],”@;\ZLS”))
Return to top
You can use the Find command on the Edit menu to locate Null values or zero-length strings. In Datasheet view or Form view, select the field in which you want to search. In the Find In Field box, type Null to find Null values, or type quotation marks (" ") with no space between them to find zero-length strings. In the Match box, select Whole Field, and make sure the Search Fields As Formatted check box isn't selected.
For more information on using the Find command, click .
Return to top