Day 21: Access Chapter 5

1. Day 21:Access Chapter 5 Tazin AfrinTazin.Afrin@mail.wvu.edu March 27, 2014

2. objectives • Establish data validation • Create input mask • Create and modify lookup table

3. Data validation • Data validation is a set of constraints or rules that help control how data is entered into a field. • Access provides some data validation automatically

4. Data validation methods • Required • Sets the Required property of a field to force data entry, such as a last name. • Default value • Specifies a value that is automatically entered into a field. • Validation rule • Limits the type of data a user can enter into a field. • Validation text • Provides the error message telling users what they did wrong and gives them instructions on what they need to do to fix it.

5. Data validation methods • Input mask • Forces to conform to a specific data entry format. • Lookup lists • Field values are limited to a predefined list of values. • Multiple value field • Accepts multiple values in a single field. • For example, an employee might have a status of both full time and temporary.

6. required • Cannot be left blank when you create a new record. • Primary keys are required fields by definition. • Default Required setting is No for all the remaining fields. • Set the Required property to Yes for critical fields. • Set in design view of table.

7. Default value • When a majority of new records contain a common value, you can set a default value for that field to reduce data entry time. • Establish a field’s default property by selecting the field in the table design view. • You can type Date() in the Default Value property to insert the current date when a new record is added. • This type of default value is useful for fields like Date of Entry, Date Record Added, and Order Date.

8. Validation rule • A validation rule is designed to restrict the data values that can be entered into a field. • Data values that do not conform are rejected. • Examples: • If all of your school’s course numbers are larger than 100, then you could establish a validation rule to prohibit values less than or equal to 100 on a registration form. • Validation rules do not make the field required

9. Validation text • Validation text informs users that the validation rule has been broken, and how to correct it. • If you violate a validation rule, Access will not let you continue until the data value conforms to the rule. • For example, the validation text for the rule violation above might be, “You have entered an invalid course number. Please recheck the number and enter a value greater than 100.”

10. Input mask • An input mask restricts the data being input into a field by specifying the exact format of the data entry. • Examples: • Phone numbers • Social Security numbers • Zip codes • Dates • Use input mask wizard.

11. Lookup field • A lookup field provides the user with a finite list of values to choose from. • For example, class status in a university’s student table would be Freshman, Sophomore, Junior, Senior, Graduate, or Other. • Use lookup wizard in design view.

12. Thank You Log Off