Validation Rules

1. Validation Rules BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

2. What are validation rules? • In Access, we can implement rules that require the user to enter only valid data in a field. • Validation rules keep the user from entering data that does not follow the “rules.” BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

3. Validation Rules can include: • Making a field required – data must be included in that field (in other words, the field cannot be left bank). • Require the data entered to be within a specified range $0 to $500. • May include a default value (i.e., GA for a state field) • Making lower case letters upper case letters • May give the user a list of acceptable choices to choose from when entering data (i.e., LPN, RN, CNA). BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

4. To make a field required . . . • In design view, click on the field name. • In the lower half of the screen – Field Properties – Next to “Required”, click the drop down arrow and choose “yes.” • The field is now required – the user may not skip entering data in the specified field. • Be sure to save, before changing views. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

5. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

6. Requiring data to be within a specified range . . . • In the design view, select the field in the upper portion of the window. • In the lower part of the screen – field properties section, click adjacent to “Validation Rule.” • We want the value to be at least 0 and at most 500. • Type in the rule area: >=0 And <=500 • This rule will allow the minimal entry to be greater than or equal to 0 and less than or equal to 500. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

7. Specified range . . . • If a user accidentally entered a value outside of the acceptable range, we want the program to give them a message. • In the field properties section, in the “Validation Text” field, type the text: “Must be at least 0 and at most 500” BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

8. For the field to have a default value . . . • While in design view, select the field that you want a default value entered. • In the field properties section next to “Default Value” type = and the default value that you want entered. (i.e., =GA – The field would automatically have the abbreviation for GA entered into it. The user could enter another value if desired, but it would have a value by default. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

9. For a list of acceptable responses . . . • To provide a list of acceptable responses, select the field in the upper section of the screen. • In the field properties section next to valudation Rule, type =CNA or =LPN or = RN • For validation text, you might type: Must be CNA, LPN or RN • Access will automatically enter quotation marks as appropriate and capitalize the words Or. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

10. Finally, to specify a format . . . • To specify the way data looks in a field, you can format a field. • To make a field always appear as all capital letters, select the field in design view. • In the field properties next to Format type a > symbol. • The > symbol will convert all text typed to upper case letters. • What do you think the < symbol will do? BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

11. One more tip . . . • If you want a field to only have 4 characters in it, you can set the field size to 4. • Doing so will keep a user from trying to enter more than 4 characters in the field. • In Design View, select the field in the upper section. • In the Field Properties section next to Field Size, type the desire number of characters. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.

12. Why use Validation Rules? • Makes sure the data is valid. • Helps make sure data is formatted correctly. • If more than one user, it keeps entering data consistent. BCS-CA2-4 Students will use database software to create, edit & publish industry appropriate files.