Chapter 2: Creating Access Tables
71
You can also use Visual Basic for Applications (VBA) code to set a field’s AllowZeroLength
property.Combining the AllowZeroLength and Required properties enables you to differentiate
between data that doesn’t exist (which you’ll probably want to represent as a zero-length string)
and data that is unknown (which you’ll want to store as a null value). In some cases, you’ll want to
store the proper value in the Text or Memo field.An example of data that doesn’t exist is the case of a customer who doesn’t have an e-mail address.
The e-mail address field should be set to an empty (zero-length) string indicating that you know
the user has an e-mail address, but you don’t know what it is. Another customer who is entirely
new to the company should have a Null value in the e-mail address field, indicating that you
don’t know whether the customer has an e-mail address.An Input Mask can help your application’s users distinguish when a field contains a Null value.
For example, the Input Mask could be set to display Ask customer when the field contains a
zero-length string, and Unknown when the value is Null.The Required property determines whether a Null value is accepted by the field, while the
AllowZeroLength property permits zero-length strings in the field. Together, these independent
properties provide the means to determine whether a value is unknown or absent for the field.The interaction between Required and AllowZeroLength can be quite complicated. Table 2.6
summarizes how these two properties combine to force the user to input a value, or to insert either
a Null or zero-length string into a field.TABLE 2.6
Required and AllowZeroLength Property Combinations
AllowZeroLength Required Data Entered by User Value Stored in Table
No No Null Null
No No Space Null
No No Zero-length string Disallowed
Yes No Null Null
Yes No Space Null
Yes No Zero-length string Zero-length string
No Yes Null Disallowed
No Yes Space Disallowed
No Yes Zero-length string Disallowed
Yes Yes Null Disallowed
Yes Yes Space Zero-length string
Yes Yes Zero-length string Zero-length string