You can vet or validate data in Access desktop databases as you enter it by using validation rules. You can use the expression builder to help you format the rule correctly. Validation rules can be set in either table design or table datasheet view. There are three types of validation rules in Access: Show
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 current field as a part of the rule unless you are using the field in a function. Restrictions on types of characters to be entered in a field may be easier to do with an Input Mask. For example, a date field might have a validation rule that disallows values in the past. Quick examples: Disallow date values in the past: >=Date() Generally accepted email format: Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) Number less than or qual to five: <=5 Currency field can't be negative: >=0 Restrict character length in string: Len([StringFieldName])<100  2. Record Validation Rule You can use a record validation rule to specify a condition that all valid records must satisfy. You can compare values across different fields using a record validation rule. For example, a record with two date fields might require that values of one field always precede values of the other field (e.g., StartDate is before EndDate). Quick examples: Ensure the end date doesn't come before the start date: [End Date]>=[Start Date] Enter a required date that occurs no more than 30 days after the order date: [RequiredDate]<=[OrderDate]+30 3. Validation on a form You can use the Validation Rule property of a control on a form to specify a criterion that all values input to that control must meet. The Validation Rule control property works like 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 no matter where it was used. In this articleOverviewThis article explains how to use validation rules and validation text in table fields and form controls. A validation rule is one way to restrict input in a table field or a control (such as a text box) on a form. Validation text lets you provide a message to help users who input data that is not valid. When data is entered, Access checks to see whether the input breaks a validation rule – if so, the input is not accepted, and Access displays a message. Access provides a number of ways to restrict input:
You can use these methods of validating data alone or in combination with each other. Data types are not optional, and provide the most basic type of data validation. For more information about data types, field sizes, and input masks, see the article Introduction to data types and field properties. Types of validation rulesYou can create two basic types of validation rules:
If the syntax for validation rules looks cryptic, see the section for an explanation of the syntax and some example validation rules. Where you can use validation rulesYou can define validation rules for table fields and for controls on forms. When you define rules for tables, those rules apply when you import data. To add validation rules to a table, you open the table and use commands on the Fields tab of the ribbon. To add validation rules to a form, you open the form in Layout view and add rules to the properties of individual controls. The steps in the section explain how to add validation rules to table fields. The steps in the section , later in this article, explain how to add rules to the properties in individual controls. What you can put in a validation ruleYour validation rules can contain expressions — functions that return a single value. You can use an expression to perform calculations, manipulate characters, or test data. A validation rule expression tests data. For example, an expression can check for one of a series of values, 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 that are less than 100. The expression ([OrderDate] - [ShipDate]) calculates the number of days that elapsed between the time an order was placed and the time it shipped. For more information about expressions, see the article Build an expression. Top of Page Add a validation rule to a tableYou can add a field validation rule and/or a record validation rule. A field validation rule checks input to a field, and is applied when the focus leaves the field. A record validation rule checks input to one or more fields is applied when the focus leaves the record. Usually, a record validation rule compares the values of two or more fields. Notes: The following field types do not support validation rules:
Create a field validation rule
|
What are three validation criteria used for a validation rule? (Choose three)
zusammenhängende Posts
Urheberrechte © © 2024 ketiadaan Inc.
