MS-Access / Getting Started

---

Defining Field Validation Rules for Web Databases

To define a simple check on the values that you allow in a field in a web database, you can enter an expression in the Validation Rule property for the field just as you can for client databases. Access won't allow you to enter a field value that violates this rule. Access performs this validation for data entered in a Table window in Datasheet view, in an updateable query, or in a form. If you publish your web database to a SharePoint server, the server will also enforce your field validation rules.

In general, a field validation expression consists of an operator and a comparison value. If you do not include an operator, Access assumes you want an "equals" (=) comparison. You can specify multiple comparisons separated by the Boolean operators OR and AND. It is good practice to always enclose text string values in quotation marks. If one of your values is a text string containing blanks or special characters, you must enclose the entire string in quotation marks.

You can use the comparison symbols to compare the value in the field to a value or values in your validation rule. Comparison symbols for web databases are summarized in Table-4. (Unlike client databases, you cannot use the Between operator in validation rules in web databases.) For example, you might want to ensure that a numeric value is always a positive number. To do this, enter >0. You can use one or more pairs of comparisons to ask Access to check that the value falls within certain ranges. For example, if you want to verify that a number is in the range of 100 through 200, enter either >=100 And <=200. Another way to test for a match in a list of values is to use the IN comparison operator.

Table-4 Comparison Symbols Used in Validation Rules for Web Databases

Operator	Meaning
NOT	Use before any comparison operator except IS NOT NULL to perform the converse test. For example, NOT > 10 is equivalent to <=10.
<	Less than
<=	Less than equal to
>	Greater than
>=	Greater than or equal to
=	Equal to
<>	Not equal to
IN	Test for equal to any member in a list; comparison value must be a comma-separated list enclosed in parentheses
LIKE	Test a Text field to match a pattern string
IS NOT NULL	Requires the user to enter a value in the field

If you need to validate a Text field against a matching pattern (for example, a postal code or a phone number), you can use the LIKE comparison operator in web databases. You can provide a text string as a comparison value that defines which characters are valid in which positions. Access understands a number of wildcard characters, characters which you can use to define positions that contain any single character, zero or more characters, or any single number. These characters are shown in Table-5 and are identical to what you can use for client databases.

Table-5 LIKE Wildcard Characters

Character	Missing
?	Any single character
*	Zero or more characters; use to define leading, trailing, or embedded strings that don't have to match any specific pattern characters
#	Any single digit

You can also specify that any particular position in the Text field can contain only characters from a list that you provide. You can specify a range of characters within a list by entering the low value character, a hyphen, and the high value character, as in [A-Z] or [3-7]. If you want to test a position for any characters except those in a list, start the list with an exclamation point (!). You must enclose all lists in brackets ([ ]).

In the Vendors table of the Restaurant Database you've been building, the EmailAddress field could benefit from the use of a validation rule. Open the Vendors table in Datasheet view, if it isn't already open, tab over to the EmailAddress field, click the Validation button in the Field Validation group, and then click the Field Validation Rule option from the dropdown list.

Access opens the Expression Builder dialog box. In the EmailAddress field, we want to be sure the email address provided by the user appears to be a valid email address. We can verify the email address meets most standards of valid syntax by using a combination of the LIKE operator and wildcard characters in a field validation rule. In the blank text box at the top of the Expression Builder dialog box, type Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) for the field validation rule. This field validation rule ensures that every email address provided by the user starts with at least one character followed by the @ symbol, contains at least one more character following the @ symbol, and contains the dot symbol followed by at least one more character after the dot symbol. Also, this field validation rule does not allow a space, a comma, or a semicolon anywhere in the email address.

Click OK to save your changes to the field validation rule and dismiss the Expression Builder dialog box. You should now add an appropriate custom validation text to display to users if they provide data to the EmailAddress field that does not pass your new field validation rule. Make sure the focus is still in the EmailAddress field, click the Validation button in the Field Validation group, and then click the Field Validation Message option from the drop-down list.