Validation rules on database fields

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A student sets the Age field in a school database to accept only numbers from 11 to 14. Someone types 25. What happens?

A The database rejects the record because it breaks the validation rule

B The database accepts 25 and fixes it to 14

2. Which field setting means a record cannot be saved if the field is left empty?

A Required field

B Lookup list

3. Which is the best way to make sure the Year Level field only holds 7 or 8?

A Hope everyone types the right number

B Add a validation rule that only allows 7 or 8

4. A Pet Type field uses a lookup list with Dog, Cat and Bird. What can a user do?

A Type any animal they like

B Pick only one of the choices from the list

5. What is the job of validation text in a database field?

A It shows a message explaining why an entry was rejected

B It stores extra notes about the record

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Required: Yes		ALookup list: Year 7, Year 8, Year 9
2Validation Rule: >=0 And <=100		BA required field cannot be left blank. The record is rejected until a name is entered.
3Show an error message when someone enters a bad value, to explain the rule		CValidation Text: Enter a mark from 0 to 100
4Limit a field so users can only pick from a lookup list of choices		DOnly numbers from 0 to 100 are accepted in this field
5Validation Rule: >=0 And <=100 Entered mark: 150		E150 is outside the range 0 to 100, so the record is rejected and must be corrected to a mark within the range.
6Required: Yes Student name: (left blank)		FThe field cannot be left empty, so every record must have a value in it

Definitions

validation rule

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Field: StudentName Required: ___

Field: Mark Validation Rule: >=0 And __100

Validation Rule: Between 0 And 100 _______________: "Enter 0 to 100"

Field: Postcode Data Type: Short Text Field Size: _

Field: YearLevel Validation Rule: __ ("Year 7","Year 8")

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Field: Age

B Data Type: Number

C Validation Rule: >=11 Or <=14

D Validation Text: Age 11-14

Question 2

A Field: FirstName

B Data Type: Short Text

C Required: No

D Max Length: 20

Question 3

A Field: Postcode

B Data Type: Short Text

C Required: Yes

D Max Length: 40

Question 4

A Data Type: Short Text

B Validation Rule: >=0 And <=100

C Validation Text: Score 0-100

Question 5

A Field: House

B Data Type: Short Text

C Control: Drop-down List

D Limit To List: No

Answer Key

Validation rules on database fields

Section A: Quick Questions

A — The database rejects the record because it breaks the validation rule

A — Required field

B — Add a validation rule that only allows 7 or 8

B — Pick only one of the choices from the list

A — It shows a message explaining why an entry was rejected

Section B: Match the Pairs

Required: Yes → The field cannot be left empty, so every record must have a value in it

Validation Rule: >=0 And <=100 → Only numbers from 0 to 100 are accepted in this field

Show an error message when someone enters a bad value, to explain the rule → Validation Text: Enter a mark from 0 to 100

Limit a field so users can only pick from a lookup list of choices → Lookup list: Year 7, Year 8, Year 9

Validation Rule: >=0 And <=100 Entered mark: 150 → 150 is outside the range 0 to 100, so the record is rejected and must be corrected to a mark within the range.

Required: Yes Student name: (left blank) → A required field cannot be left blank. The record is rejected until a name is entered.

Section C: Definitions

validation rule — A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.

Section D: Fill in the Blanks

Field: StudentName
Required: Yes

Field: Mark
Validation Rule: >=0 And <=100

Validation Rule: Between 0 And 100
Validation Text: "Enter 0 to 100"

Field: Postcode
Data Type: Short Text
Field Size: 4

Field: YearLevel
Validation Rule: In ("Year 7","Year 8")

Section E: Spot the Error

C — With Or, every number passes because it is either over 10 or under 15. The rule should use And so only ages from 11 to 14 are accepted.

C — Required: No lets the field be left empty. It should be Required: Yes so a record with no first name is rejected.

D — An Australian postcode has 4 digits, so a limit of 40 characters lets bad entries through. It should be Max Length: 4.

A — A number range rule needs a Number field. A Short Text field cannot be checked properly as a range, so the data type should be Number.

D — If Limit To List is No, a user can still type a value that is not in the list. It should be Yes so only the listed choices are accepted.