Part I: Access Building Blocks
70
entered for values such as inventory quantities, and that entries are restricted to different ranges of
numbers or text.Dates in a Validation Rule property are surrounded, or delimited, by pound signs (#) when
used in Access expressions. If you want to limit the LastSalesDate data entry to dates between
January 1, 2012, and December 31, 2013, enter Between #1/1/2012# and #12/31/2013#.Tip
If you want to limit the upper end to the current date, you can enter a different set of dates, such as Between
#1/1/2010# and Date(). Date() is a built-in VBA function that returns the current date; it’s completely
acceptable as part of a validation rule or other expression.
When a field is dragged onto a form, the Validation Rule property of the new control is not
set to the field’s Validation Rule. Unless you enter a new Validation Rule value in the
control’s Property Sheet, Access enforces the rule set at the table level.Field and control Validation Rule properties are enforced when the focus leaves the table field
or form control. Validation Rule properties applied to both a field and a control bound to the
field are enforced for both entities. The table-level rule is applied as data is edited on the bound
control and as focus leaves the control.You can’t create table-level Validation Rule properties for linked “foreign” tables, such as
FoxPro, Paradox, or dBASE. Apply Validation Rule properties to controls bound to fields in
linked foreign tables.Required
The Required property instructs Access to require input into the field. When set to Yes, input is
required in the field within a table or in a control on a form bound to the field. The value of a
required field can’t be Null.The Required property is invalid for AutoNumber fields. By default, all AutoNumber fields are
assigned a value as new records are created.The Access database engine enforces the Required property. An error message is generated if the
user tries to leave a text box control bound to a field with its Required property set to Yes.The Required property can be used in conjunction with the Allow Zero Length property to
determine when the value of a field is unknown or doesn’t exist.AllowZeroLength
The AllowZeroLength property specifies whether you want a zero-length string (““) to be a
valid entry for a Text or Memo field. AllowZeroLength accepts the following values:l (^) Yes: A zero-length string is a valid entry.
l No: The table will not accept zero-length strings, and instead, inserts a Null value into the
field when no valid text data is supplied.