Data Validation

To limit the kind of information or values that users enter into cells, utilise data validation. Data validation, for instance, may be used to determine a cell's maximum allowable value based on a number found elsewhere in the workbook. The user entered abc in the following example, which is not a valid value for that cell.

Data Validation Showing Invalid Input

Types of Data Validation

MS Access enables data validity verification at the time of data entry. Applying your own validation rules is possible. Two forms of data validation exist.

• Field Level: Field Level Permits verifying data that is being entered into a specific field.

• Data From: Data from several fields may be validated at the record level.

The following data validation types and strategies are supported by MS Access.

Data Types

It is one of the easiest methods for validating data. Access facilitates data entry based on the field's data type. It forbids inserting the wrong kind of data in a field.

Mask Input Property

Mask Input Property is very useful for making data entry simpler and limiting the quantities a user may enter in a text field.

Text Property and the Validation Rule

While adding, changing, or saving data in a field, validation rules validate the data. The Validation Text specifies the text of the message that appears if the field, control, or record does not satisfy.

Permit Zero Length Property

The Allow Zero Length property is used to designate a zero-length string (" ") as a permitted entry in a field. Only the Text, Memo, and Hyperlink table fields are affected by this feature. When leaving a field empty, you may need to save a string of zero length rather than a Null value. The Allow Zero Length and Required attributes must be set to yes in this situation.

Solved Questions

1. With an example, describe data validation in Microsoft Excel (data validation examples).

Ans: Excel has a function called data validation that limits what a user may type into a cell. Data validation can be used, for instance, to ensure that a value is a number between 1 and 6, that the date happens within the next 30 days, or that a text entry is no longer than 25 characters.

Message Displayed by Data Validation

2. How to create a validation rule (data validation rules)?

Ans: 1. Choose the cell or cells that you wish a rule to apply to.

2. Select Data >Data Validation should be selected.

Data Validation Tool

3. Choose an option under Allow on the Settings tab:

• Whole Number - to limit the cell to only accept whole numbers.

• Decimal - This limits the cell's acceptability to decimal numbers only.

• List - To select information from a drop-down menu.

• Date - To limit the cell's acceptability to just dates.

• Time - To limit the cell to just accepting only time.

• Text Length - To limit the text's length.

• Custom - For a custom formula.

4. Select a condition under Data.

5. Based on the options you selected for Allow and Data, set the additional necessary values.

6. Customize the message users will see while inputting data by selecting the Input Message tab.

7. To display the message when a user chooses or hovers over a chosen cell, tick the Show input message when the cell is selected box (s).

8. To edit the error message and select a Style, select the Error Alert tab.

9. Choose OK.

Learning by Doing

Write True or False.

1. Excel's data validation tool is not used to limit what users may enter into a cell.  (T/F)

2. Field Level Permits verifying data that is being entered into a specific field.  (T/F)

3. Text, Memo, and Hyperlink table fields are affected by the Text type method.  (T/F)

4.  Excel's data validation tool enables implementing certain rules via data validation. (T/F)

Summary

Data validation can be used to restrict the types of data or values that users submit into fields. When a cell is selected, it prompts users to input proper data and displays an error notice if they do not. Users can also display a customised alert if they attempt to submit erroneous data. When entering data, MS Access allows for the validation of that data. It is possible to use your own validation rules. For example, data validation can be used to guarantee that a value is a number between 1 and 6, that date will occur within the next 30 days, or that a text entry is no longer than 25 characters.