Data Validation (Grade 12 NSC Matric Computer Application Technology): Revision Notes
Data Validation
What is data validation?
Data validation is the process of checking whether information is accurate, in the correct format, and the correct type before allowing the database to store it. Think of it as a quality control system that ensures your database only contains reliable, consistent data.
When you set up validation rules, you're essentially creating a set of standards that all data must meet before it can be saved. This prevents errors, maintains data integrity, and makes your database much more reliable for generating reports and making decisions.
Data validation acts as a gatekeeper for your database. Without proper validation, you might end up with inconsistent data like ages of 200 years, phone numbers with letters, or email addresses without @ symbols. This corrupted data can make reports unreliable and cause serious problems in decision-making.
Types of validation checks
There are several different ways you can validate data in Access databases:
Data type check
This ensures that the information entered matches the expected data type. For example, if a field is set to store numbers, it won't allow someone to type letters. Similarly, date fields will only accept properly formatted dates.
Digit or length check
This validation ensures that data contains a specific number of characters. This is particularly useful for things like phone numbers, ID numbers, or postal codes where you need exactly the right number of digits.
Range check
Range validation ensures that numerical data falls within specific limits. For example, you might set a percentage field to only accept values between 0 and 100, or an age field to only accept values between 0 and 120.
Format check
Format validation ensures that data follows a specific pattern. This is especially important for things like email addresses, which must contain an @ symbol and end with a proper domain extension like .com or .org.
Lookup column
This presents users with a dropdown list of predefined values to choose from. It prevents typing errors and ensures consistency by limiting choices to valid options only.
Validation rules
These are custom expressions that you create to specify exactly what requirements data must meet. The system checks these rules before allowing data to be saved.
Required properties
This setting prevents fields from being left blank when they contain essential information that must always be provided.
Input masks
Input masks guide users during data entry by showing them exactly how information should be formatted, such as displaying placeholders for phone numbers or dates.
Setting up validation rules in Access
To add validation rules to your database fields, follow these steps:
- Open your database and navigate to the table containing the data
- Go to Design View to modify the table structure
- Select the field where you want to add validation protection
- In the Fields tab, locate the Validation section in the ribbon
- Choose "Field Validation Rule" from the dropdown menu
- The Expression Builder will open, allowing you to create your validation rule using appropriate operators and expressions
- Type your validation rule in the text box using the proper syntax
- Click OK to save your validation rule
After setting up the rule, you should also add a custom error message that will appear when someone tries to enter invalid data. A validation rule without a helpful error message can confuse users and make data entry frustrating.
Common validation rule examples
Here are some practical validation rules you can use in your databases:
Practical Validation Rules
| Validation Rule | Purpose | Example Use |
|---|---|---|
<>0 | Must enter a non-zero value | Quantity fields where zero isn't allowed |
>=0 | Value must be zero or greater | Age, price, or count fields |
BETWEEN 0 AND 1 | Value must be between 0 and 1 | Percentage fields stored as decimals |
<Date() | Date must be in the past | Birth dates that cannot be in the future |
M OR F | Must enter M for male or F for female | Gender code fields |
LIKE "*@*.com" OR "*@*.net" OR "*@*.org" | Must be a valid email format | Email address fields |
These examples show how validation rules use comparison operators and functions to check data before it's saved.
Input masks for data formatting
Input masks help users enter data in the correct format by providing a template that shows exactly how information should look. When you set up an input mask, users see placeholder characters that guide their typing.
Understanding Input Mask Characters
Think of input masks as templates that show users exactly where to type each character. The mask characters act like guides - some require specific types of input (like digits only) while others are optional.
Input mask characters
| Character | Meaning | Example |
|---|---|---|
0 | User must enter a digit (0-9) | Phone numbers, postal codes |
9 | User can enter a digit (0-9) but it's optional | Extension numbers |
L | User must enter a letter | Name fields, country codes |
? | User can enter a letter (optional) | Middle initials |
A | User must enter a letter or digit | Product codes |
& | User must enter either a character or space | Text fields with required input |
When you apply an input mask, the field will show the formatting template, making it much easier for users to enter data correctly the first time.
Validation error messages
Setting up helpful error messages is just as important as creating the validation rules themselves. When someone tries to enter invalid data, your custom message should clearly explain what went wrong and how to fix it.
To add a validation message:
- After creating your validation rule, select "Field Validation Message" from the dropdown
- Type a clear, helpful error message that explains what type of data is expected
- Keep the message simple and specific to help users understand exactly what they need to correct
Writing Effective Error Messages
Instead of generic error messages like "Invalid data," write specific guidance such as "Please enter a valid birthdate" or "Age must be between 0 and 120 years." Clear messages help users fix their mistakes quickly rather than leaving them confused about what went wrong.
Key Points to Remember:
-
Data validation prevents errors - It stops incorrect information from being saved in your database, maintaining data quality and reliability
-
Multiple validation types work together - You can combine different validation methods like data type checks, range limits, and format requirements on the same field
-
Clear error messages help users - When validation fails, helpful error messages guide users to enter the correct information rather than leaving them confused
-
Input masks improve data entry - They show users exactly how to format their input, reducing mistakes and improving consistency
-
Validation rules use expressions - Understanding basic operators like
<>,>=,BETWEEN, andLIKEhelps you create effective validation rules for any situation