Data validation makes Access test data to make sure that it conforms to what you want to appear in the table. Data validation works best in number, currency, and date/time fields. You can create a validation rule for text entries, but doing so can be complicated—especially if you want to test a lot of text variables.
There are actually two boxes that relate to data validation:
- Validation Rule box: Used to specify the requirements for data entered in the field.
- Validation Text box: Used to specify the message that will be displayed to the user when data that violates the validation rule is entered.
- In Design View, click the field you want to validate.
- Click the Validation Rule box.
- Enter an expression to validate the field’s data.
The table at the bottom of the page describes some data validations that you can modify and use in your tables.
Next, you’ll need to specify the error message that Access will display if someone tries to break your validation rule.
- Click the Validation Text box.
- Type the error message text.
Now, whenever a user violates the validation rule, Access will display the validation text.
Consider the table below a “cheat sheet.” It contains samples of the most common types of validation rules. Feel free to copy, modify, or mix and match these examples to create your own validation rules.
|Data Validation Examples|
|<100||Must be less than 100.|
|<=100||Must be less than or equal to 100.|
|Between 1 and 10||Must be between 1 and 10.|
|<>0||Must not be 0.|
|<1/1/95||Must be a date after 1/1/95.|
|>=Date()||Must be today’s date or after.|
|<=Date()||Must be today’s date or earlier.|
|“Business” or “Pleasure” or “Other”||Must be “Business” or “Pleasure” or “Other.”|
|Like “??”||Must have two characters.|
|Like “####”||Must have four digits.|
FREE Quick ReferenceClick to Download
Free to distribute with our compliments; we hope you will consider our paid training.