6
To build a custom format:
1 With the table open in Design view, select the field you want to apply the custom
format to.
2 In the Field Properties area, in the Format box, enter the format.
3 Switch to Datasheet view, saving the table.
Any existing data will be displayed according to the format. New data you enter will
conform to the format when you leave the field. As an example, consider the following
format:
@”.com”;”no link”[red]
This format specifies two customizations separated by a semicolon. The part before the semicolon specifies what Access should do if characters are entered—in this case,
append .com; and the part after the semicolon specifies what Access should do if the field is empty—in this case, display no link in red.
Validating the data
A validation rule precisely defines the information that will be accepted in one or several fields in a record. You might use a validation rule in a field containing the date an em- ployee was hired to prevent a date in the future from being entered. Or if you deliver
orders to only certain local areas, you might use a validation rule on the postal code field
to refuse entries from other areas. You can create validation rules for all data types except AutoNumber, OLE Object, and Attachment.
In a table, you might want to create validation rules for individual fields or for entire
records:
▪
Field validation At this level, Access uses the validation rule to test an entry whenyou attempt to leave the field.
▪
Record validation At this level, Access uses the rule to test the contents of moreIf a field or record doesn’t satisfy the rule, Access rejects the entry and displays a message
explaining why.
TIP If you create a form by using one of the commands in the Form group on the Create tab, the form’s controls inherit any validation rules set for the corresponding fields in the table on which the form is based. To prevent errors likely to be introduced by inexperienced users of the form, you can add more restrictive rules to the form’s controls, in the same way you set rules for table fields. For information, search on validation in Access Help.
You create a validation rule by building an expression. In Access jargon, the term expres-
sion is synonymous with formula. It is a combination of operators, constants, functions, and
identifiers that evaluates to a single value. Access builds a formula in the format a=b+c,
where a is the result and =b+c is the expression.
TIP In addition to using expressions as validation rules, you can use them to assign proper- ties to tables or forms, to determine values in fields or reports, to define a set of conditions that a record must meet to be included in the result of a query, and so on. For information about queries, see Chapter 7, “Create queries.”
The expression you use in a validation rule combines multiple criteria to define a set of con-
ditions that a value in a field must meet in order to be a valid entry for that field. Multiple
criteria are combined using logical, comparison, and arithmetic operators. Different types of expressions use different operators. The following are the most common operators:
▪
Logical operators▪
And Selects records that meet all the specified criteria▪
Or Selects records that meet at least one of the criteria▪
Not Selects records that don’t match the criteria▪
Comparison operators▪
< Less than▪
> Greater thanValidating the data 175
6
You can combine these basic operators to form the following:
▪
<= Less than or equal to▪
>= Greater than or equal to▪
<> Not equal toThe Like operator is sometimes grouped with the comparison operators and is used to test whether or not text matches a pattern.
▪
Arithmetic operators▪
+ Add▪
- Subtract▪
* Multiply▪
/ DivideA related operator, & (a text form of +) is used to concatenate (combine) two text strings.
You can enter validation rules in the Validation Rule property box by hand, or you can use a tool called the Expression Builder to create them. The Expression Builder isn’t a wizard; it doesn’t lead you through the process of building an expression. It provides a hierarchical list of common elements that you might want to include in an expression, and an expres- sion box to build the expression in. To open the Expression Builder dialog box, click the Validation button in the Field Validation group on the Fields tool tab, and then click either Field Validation Rule or Validation Rule. In the dialog box, either select functions, operators, and other elements from the list to copy them into the expression box, or enter the expres- sion directly in the expression box.
To explain a validation rule to users, you can create a message that appears if someone tries to enter an invalid value. A well-crafted message tells users what data is expected and what format it should be entered in. For example, the message Please enter a whole number between 1 and 99 is more useful than Invalid entry.