The data entered in tables must be accurate if the database is to be valuable to you or your organization. Even the most experienced data-entry operators occasionally enter incorrect information. To add simple tests for the reasonableness of entries, add short expressions as a Validation Rule in the General page of Table Design view's Field Properties pane. If the entered data fails to pass your validation rule, a message box informs the operator that a violation occurred. You can customize the error message by adding the text as the value of the Validation Text property. Validating data maintains the entity integrity of your tables. Expressions are a core element of computer programming. Jet lets you create expressions without requiring that you be a programmer, although some familiarity with a programming language is helpful. Expressions use the familiar arithmetic symbols +, , * (multiply), and / (divide). These symbols are called operators because they operate on (use) the values that precede and follow them. These operators are reserved symbols in VBA. The values operated on by operators are called operands. You (greater than) operator> can also use operators to compare two values; the < (less than) and > (greater than) symbols are examples of comparison operators. And, Or, Is, Not, Between, and Like are called logical operators. (Between and Like are Jet, not VBA, operators, so they don't appear in bold type). Comparison and logical operators return only True, False, and no value (the Null value). The & operator combines two text entries (character strings or just strings) into a single string. To qualify as an expression, at least one operator must be included. You can construct complex expressions by combining the different operators according to rules that apply to each operator involved. The collection of these rules is called operator syntax.
To learn more about Access operators, see "Understanding the Elements of Expressions," p. 357. Data validation rules use expressions that result in one of two values: True or False. Entries in a data cell are accepted if the result of the validation is true and rejected if it's false. If the data is rejected by the validation rule, the text you enter as the Validation Text property value appears in a message box. Chapter 10 explains the syntax of Jet validation expressions. Note SQL Server substitutes CHECK constraints for the Validation Rule property. As with Jet, CHECK constraints can apply at the table or field level. The Jet Expression Service lets you use VBA functions, such as UCase(), in validation rules. SQL Server requires use of Transact-SQL (T-SQL) functions for CHECK constraints. Chapter 21, "Moving from Jet Queries to Transact-SQL," explains how to translate Jet/VBA functions into T-SQL functions. Adding Field-Level Validation RulesValidation rules that restrict the values entered in a field and are based on only one field are called field-level validation rules. Table 6.6 lists the simple field-level validation rules used for some fields in the HRActions table you created in Chapter 5, "Working with Jet Databases and Tables."
Note You must allow ScheduledDate values as early as 1992 to accommodate the hire dates in the first two records of the Employees table. Microsoft hasn't updated Northwind.mdb data since releasing Access 2000. The last OrderDate in the Orders table has remained May 6, 1998 in the last three Access versions. Tip The In operator simplifies expressions that otherwise would require multiple Or operators. For example, using the Or operator for the Validation Rule property value of the ActionType field requires typing "H" Or "Q" Or "Y" Or "S" Or "R" Or "B" Or "C" Or "T", which has many more characters. In their present form, the validation rules for fields that require employee ID numbers can't ensure that a valid ID number is entered. You could enter an EmployeeID number that isn't present in the Employees table. A validation rule for the EmployeeID field could test the EmployeeID number field of the Employees table to determine whether the EmployeeID number is present. You don't need to create this test because the rules of referential integrity perform this validation for you. Validation rules for InitiatedBy and ApprovedBy require tests based on entries in the Employees table.
To review referential integrity rules, see "Working with Relations, Key Fields, and Indexes," p. 188. To add the validation rules of Table 6.6 to the HRActions table, follow these steps:
You test your validation rule entries later in the "Testing Field and Table Validation Rules" section. Adding Table-Level Validation Rules with the Expression BuilderOne field, EffectiveDate, requires a validation rule that depends on ScheduledDate's value. The effective date of the personnel department's action shouldn't be before the scheduled date for the review that results in the action. You can't refer to other field names in a Jet validation rule expression; instead, you add such validation rules in the Table Properties window. Validation rules in which the value of one field depends on a previously entered value in another field of the current record are called table-level validation rules. The following steps add a table description and create a table-level validation rule for the EffectiveDate field:
Adding a Simple Lookup List to the ActionType FieldLookup tables require queries, which are the subject of this book's Part III, "Transforming Data with Queries and PivotTables." However, you can add a lookup list to the table by adding a set of values that's similar to a validation expression. You create a lookup list by selecting the field the list applies to in Table Design view, and specifying property values in the Lookup page of the Field Properties pane. To add a combo box lookup list to the ActionType field of the HRActions table, do this:
Lookup lists are handy for adding codes and other field values that seldom, if ever, change. When entering data, you can use the lookup list or type the code letter; the latter is considerably faster. You must keep lookup lists up to date and, if multiple tables use the same list, update each table's list manually. Lookups from tables, which you create with queries, are one of the topics of Chapter 11, "Creating Multitable and Crosstab Queries," are the preferred method for creating lookup tables. You can use a single lookup table for all tables that need the table's lookup values. |