Validating Data Entry

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 Rules

Validation 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."

Table 6.6. Validation Criteria for the Fields of the HRActions Table

Field Name

Validation Rule

Validation Text

EmployeeID

>0

Please enter a valid employee ID number.

ActionType

In("H","Q","Y","S","R","B","C","T")

Only H, Q, Y, S, R, B, C, and T codes are valid.

Initiated By

>0

Please enter a valid supervisor ID number.

ScheduledDate

Between Date()-5475 And Date()+365

Scheduled dates can't be more than 15 years ago or more than 1 year from now.

ApprovedBy

>0 Or Is Null

Enter a valid manager ID number or leave blank if not approved.

EffectiveDate

None

 

HRRating

Between 0 And 9 Or Is Null

Rating range is 0 for terminated employees, 1 to 9, or blank.

NewSalary

None

None.

NewRate

>5.5 Or Is Null

Hourly rate must be more than the minimum wage.

NewBonus

None

None.

NewCommission

<=0.1 or Is Null

Commission rate can't exceed 10%.

HRComments

None

None.

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:

  1. Open the HRActions table, if it isn't already open, by double-clicking its name in the Database window's tables list.

  2. graphics/design_view.gif Click the Design View button. The EmployeeID field is selected.

  3. Press F6 to switch to the Field Properties window, and then move to the Validation Rule text box.

  4. Type >0 and move to the Validation Text text box.

  5. Type Please enter a valid employee ID number. The text scrolls to the left when it becomes longer than can be displayed in the text box. To display the beginning of the text, press Home. Press End to position the cursor at the last character. Figure 6.4 shows your entries in the Field Properties text boxes.

    Figure 6.4. The Field Properties pane displays the first Validation Rule and Validation Text property values entered from the data in Table 6.6.

    graphics/06fig04.jpg

  6. Press F6 to switch back to the Table Design grid. Move to the next field and press F6.

  7. Enter the validation rule and validation text for the seven remaining fields listed in Table 6.6 that use data-entry validation, repeating steps 4 through 6.

You test your validation rule entries later in the "Testing Field and Table Validation Rules" section.

Adding Table-Level Validation Rules with the Expression Builder

One 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:

  1. graphics/properties_window.gif Click the Properties toolbar button to display the Table Properties dialog.

  2. Type Human Resources Department Actions in the Description text box (see Figure 6.5).

    Figure 6.5. When you move the cursor to the Validation Rule row of the Table Properties dialog, the ellipsis button appears so you can open the Jet Expression Builder.

    graphics/06fig05.gif

  3. graphics/builder.gif In the Validation Rule text box, click the ellipsis (Builder) button to display the Expression Builder dialog. The current table, HRActions, is selected in the lefthand list, and the fields of the table appear in the center list.

  4. Double-click EffectiveDate in the center list to place [EffectiveDate] in the Expression Builder's text box at the top of the dialog. Square brackets surround field names to distinguish them from literal (string) values.

  5. Type >= in the text box and double-click ScheduledDate in the center list to add [ScheduledDate] to the expression.

  6. To accept a blank entry if the effective date of the personnel action isn't scheduled, add Or [EffectiveDate] Is Null to the expression, which appears as shown in Figure 6.6.

    Figure 6.6. You can use the Jet Expression Builder to generate more complex expressions for use as table-level validation rules.

    graphics/06fig06.gif

  7. Click OK to add the table-level validation rule and close the Expression Builder dialog.

  8. In the Validation Text text box, type Effective date must be on or after the scheduled date. Your Table Properties window appears as shown in Figure 6.7.

    Figure 6.7. Closing the Expression Builder adds the expression of Figure 6.6 as the Validation Rule property value of the table.

    graphics/06fig07.gif

  9. Close the Table Properties dialog.

Adding a Simple Lookup List to the ActionType Field

Lookup 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:

  1. graphics/design_view.gif In Design view, select the ActionType field and click the Lookup tab in the Field Properties pane.

  2. Open the Display Control list and select Combo Box or List Box. This example uses a combo box.

  3. Open the Row Source Type list and select Value List.

  4. Accept the first column (1) default in the Bound Column list, and type 2 in the Column Count text box. The value list needs code and description columns.

  5. In the Column Widths text box type 0.3";1.2", and in the List Width text box type 1.5".

  6. Type Y(es) in the Limit to List text box. Limiting entries to the list's items is equivalent to the validation rule you added for this field.

  7. Return to the Row Source property and type H;Hired;Q;Quarterly Review;Y;Yearly Review;S;Salary Adj.;R;Hourly Rate Adj.;B;Bonus Adj.;C;Commission Adj.;T;Terminated in the text box. The semicolons separate the entries that add in pairs to create the list. Your lookup list design appears as shown in Figure 6.8.

    Figure 6.8. You create lookup lists by specifying a combo box or list box, and setting its properties in the Lookup page of the Field Properties pane.

    graphics/06fig08.jpg

  8. graphics/opening_table_displays.gif Return to Datasheet view and save your design changes.

  9. Press Tab to move to the ActionType field and press F4 to open the lookup list (see Figure 6.9).

    Figure 6.9. The lookup list for ActionType codes has a description for each of the eight valid codes.

    graphics/06fig09.gif

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.



Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net