Data validation can be performed data various level in ms access software. However we shall be limited only to field level and record level validation. The field level validation validates the data being entered in an invalid field and the record level validation validates the data of more than one field w.r.t. to another so as to ensure correct data.
E. g. In a table the joining date and retirement date of an employee cannot be same and also retirement dare cannot be less than joining date. Such validation is known as Record Level Validation. Before discussing the record level validation let us first discuss the field level validation techniques.
Field Level Validation in MS Access
As you know that the field level validation ensures valid data in a single field. At field level following validation techniques may be used:
Input mask, Validation Rule and Text, Default value, Aloe zero length, Required, Lookup values
These validation techniques can be applied field through their properties that are accessible in DESIGN VIEW. Let us learn about these techniques one by one. Later we’ll learn to use them in a table.
Input Mask
The input mask property setting specifies how data is entered and displayed. E.g. if you set this property to –, hyphens are displayed as shown and underscore (_) is displayed in place of each zero i.e. at the same time of Data Entry it will be displayed as_ _ _ _ _ _- _ _ -_ _
MS Access Software
The setting can be containing up to three parts separated by semicolons (for e.g., (999) 000-0000! 0; ““):
v The setting can contain up to three parts separated by semicolons for example (999) 000-0000 😉
v The first part specifies the input mask itself
v The second part specifies the weather Microsoft Access Stores the literal display characters in the table when you enter data. If you use 0 for this part all literal display characters are stored with the value; if you enter 1 or leave third part blank, only characters typed into the text box are stored.
The third part specifies the characters the Microsoft Access displays for spaces in the input mask. For this part, you can use a spaces enclosed in quotations characters to display (‘‘).
Validation Rule and Text in MS Access Software
Validations rule validate data hen data in a field is added or changed or, when you save record.
Ms Access Software
Validations text specifies the text of the messages that appears if the field control or record dosen’t satisfy the conditions listed in the validations rule setting.
If you set the validation rule property, but not the Validation Text Property of MS-Access displays a standard error messages when the validation rule is violated.
You can use the validation rule and validation text properties to help the user enter valid data. For example, when a record in the short date field falls between the company founding date and the current date. If the start date entered in this range you can display the message; start date is incorrect by typing these messages in the validation text field.
Default VALUE
You can use the default value property to specify a value that to automatically entered in a field when a new record is created. E,g. in an address table you can set the default value for the city field to NEW DELHI.
When the user add a new record to the table, they can either accept this value or enter the name of different city.
The DEFAULT VALUE property doesn’t apply to check box, option button, or the option group itself. The default value property applies the all the table fields expect those fields with the data of Auto number or OLE object.
Allow Zero Length
Allowing zero length means that the use can enter a zero length string in a field (‘””)
If zero lengths are not allowed then the field must either contain a value or a null?
This property is closed related to the required property which you are going to learn in the following subsections
The allow zero length property uses the following settings;
| Setting | Description |
| yes | A zero-length string is a valid entry |
| no | A zero-length string is an invalid entry |
Required Value
The required value property specifies whether or not a value is required in a field. If this property is set to yes then you must enter a value in the field or in any control bound to the field at the time of data entry.
You can use this property to ensure that a particular filed or control bound to a filed always has a value for each record. In what case you may set the required property for Last Name yes.
You can use the setting of the required and allow zero length properties to different between information that did not exit.
Lookup Value
The lookup values list refers to the list of values where from a value can be picked. These values can either be user specifies or can come from another table or query.
Lookup List
A list that displays values looked up from a related table is called lookup list
For instance, if in a school database, there is a table subject as follows:
| Sub Code | Subject Description | No. Of Periods |
| 101 | English | 40 |
| 102 | Math | 42 |
| 103 | IIT | 35 |
| : | : | : |
| : | : | : |
| : | : | : |
And while making entries in teacher table, you can choose one of the valid subjects coming from subject table. In such a case, a lookup list is useful.
Leave a Reply