Forces users to enter values in the field. This is equivalent to setting the Required Field property to Yes. However, if you enable the Required property and a user cannot enter a value, Access displays a somewhat hostile error message. In general, the database is easier to use if you use IS NOT NULL and enter a message in the Text Validation property. 3. Validation in a form You can use the validation rule property of a control on a form to specify a criterion that all values entered in that control must meet. The validation rule control property functions as a field validation rule. Typically, you use a form validation rule instead of a field validation rule if the rule was specific only to that form and not to the table, regardless of where it was used. You can use the comparison icons to compare the value of the field with one or more values in your validation rule. The comparison symbols are summarized in Table 4. For example, you can make sure that a numeric value is always less than 1000.
To do this, enter =50 and <=100, or between 50 and 100. Another way to test a match in a list of values is to use the IN comparison operator. For example, to search for states around the U.S. capital, type In ("Virginia," "Maryland"). If you just need to make sure that the user enters a value, you can enter the special comparison phrase Is not null. Enter a value with a percentage sign. (Use with a field that stores numeric values as percentages.) To define a simple check of the values that you allow in a field, type an expression in the Field validation rule property box. In Access 2010, you cannot enter a field value that violates this rule. Access performs this validation on data entered in a table window in Datasheet view, in an editable query, or on a form. You can specify a more restrictive validation rule on a form, but you cannot override the rule defined for the field in the table by specifying a completely different rule on the form.
The date field now requires values earlier than 2010, but the form control requires that dates have this year or later, so you cannot enter dates at all. Record validation rules Use a record validation rule to control when you can save a record (a row in a table). Unlike a field validation rule, a record validation rule refers to other fields in the same table. You create record validation rules when you need to compare values in one field with values in another range. Let`s say your company asks you to ship the products within 30 days and if you don`t ship within that time, you`ll have to refund your customer a portion of the purchase price. You can set a record validation rule such as [RequiredDate]<=[OrderDate]+30 to ensure that someone does not enter a shipping date (the value in the RequiredDate field) too far in the future. Input masks You can use an input mask to validate data by forcing users to enter values in a specific way. For example, an input mask might force users to enter dates in a European format, such as 2007.04.14. We recommend that you always enclose text string values in quotation marks. If one of your values is a text string that contains spaces or special characters, you must enclose the entire string in quotation marks. For example, to limit valid entries in a City field to the two largest cities in upstate New York, type Los Angeles or San Diego.
When comparing dates, you must place the date constants in pound signs (#), as in #07/1/2010#. Tests a range of values. You must use two comparison values (low and high) and separate these values with the AND separator. A validation rule is an expression that restricts the values that can be entered in the field. The rule is applied once all fields have been entered, just before the record is saved. Use this rule to compare values in different fields or to delay validation until the last moment before saving the record. 1. Field validation rule You can use a field validation rule to specify a criterion that all valid field values must meet. You should not have to specify the active field as part of the rule unless you are using the field in a function.
Restrictions on the types of characters that must be entered in a field can be more easily implemented with an input mask. For example, a date field might have a validation rule that does not allow values in the past. Tests values that match existing items in a list. The comparison value must be a comma-separated list in parentheses. You can add a field validation rule and/or a record validation rule. A field validation rule validates the entry in a field and is applied when the focus leaves the field. A record validation rule checks whether entries are applied to one or more fields when the focus exits the recording. Typically, a record validation rule compares the values of two or more fields.
Tests inverse values. Use null before any comparison operator except IS NOT. Next, you need to provide the error message that Access displays when someone tries to violate your validation rule. 2. Record validation rule You can use a record validation rule to specify a condition that all valid records must meet. You can compare values between different fields by using a record validation rule. For example, a record with two date fields might require that the values in one field always precede the values in the other field (for example, StartDate is before EndDate). Typically, a field validation expression consists of an operator and a comparison value. If you don`t include an operator, Access assumes that you want an « equal » (=) comparison.
You can specify multiple comparisons separated by the Boolean operators OR and ET. Your validation rules can contain expressions, that is, functions that return a single value. You can use an expression to perform calculations, change characters, or test data. A validation rule expression tests the data. For example, an expression might search for one of the values of a set, such as « Tokyo » or « Moscow » or « Paris » or « Helsinki ». Expressions can also perform mathematical operations. For example, the expression <100 forces users to enter values less than 100.
