Microsoft Office Tutorials and References
In Depth Information
6. Click the expand indicator for the qryCustomersByName query in the Object
Dependencies pane. The list expands to display all the tblCustomer table, which is
another table that the query depends upon.
7. Close the tblContract table, close the Object Dependencies pane, and then close
the Navigation Pane.
Sarah now better understands object dependencies and how to identify them by using
the Object Dependencies pane.
Defining Data Validation Rules
Sarah wants to limit the entry of Zip field values in the tblCustomer table to Michigan zip
codes because Belmont Landscapes customers are located only in Michigan. In addition,
Sarah wants to make sure that a SigningDate field value entered in a tblContract table record
is chronologically earlier than the StartDate field value in the same record. She’s concerned
that typing errors might produce incorrect query results and cause other problems. To pro-
vide these data-entry capabilities, you’ll set field validation properties for the Zip field in the
tblCustomer table and set table validation properties in the tblContract table.
Defining Field Validation Rules
To prevent a user from entering an incorrect value in the Zip field, you can create a field
validation rule that verifies a field value by comparing it to a constant or a set of
constants. You create a field validation rule by setting the Validation Rule and the
Validation Text field properties. The Validation Rule property value specifies the valid
values that users can enter in a field. The Validation Text property value will be displayed
in a dialog box if the user enters an invalid value (in this case, a value other than a
Michigan zip code). After you set these two Zip field properties in the tblCustomer table,
Access will prevent users from entering an invalid Zip field value in the tblCustomer
table and in all current and future queries and forms that include the Zip field.
You’ll now set the Validation Rule and Validation Text properties for the Zip field in the
tblCustomer table. Michigan zip codes must be between 48000 and 49999.
To create and test a field validation rule for the Zip field:
1. Switch to Design view for the tblCustomer table, and then click the Zip Field
Name text box to make that row the current row.
To make sure that the only values entered in the Zip field are between 48000 and
49999, you’ll specify a range of valid values in the Validation Rule text box. The Zip
field is a Number field, so you can use this numeric range test.
2. In the Field Properties pane, click the Validation Rule text box, type >=48000
And <=49999 , and then press the Tab key.
Instead of using the range of values test, you could also use the equivalent
Between 48000 And 49999 .
You can set the Validation Text property to a value that appears in a dialog box that
opens if a user enters a value not listed in the Validation Rule text box.
3. Type Michigan zip codes must be between 48000 and 49999 in the Validation
Text text box. See Figure 5-41.
Using the numeric range
test with a Text field isn’t
allowed; you’ll get an error
message. Also, enclosing
the numbers in quotation
marks for a Text field
range test will eliminate
the error message but will
yield unpredictable results.