Table Design  «Prev 

Showing the Validation Rule property

Question: How do I use the validation rule property to test data in Microsoft Access?
In Microsoft Access, the Validation Rule property is used to enforce specific constraints on the data that can be entered into a field in a table or a control in a form. The system will prevent the entry of data that fails to meet the conditions set in the rule. Here's a step-by-step process to set up a validation rule:
  1. Open the relevant table in Design View. From the Navigation Pane, right-click the table you wish to apply validation to and select "Design View." This view allows you to alter the structure of your table.
  2. Select the field for validation. Click on the field (column) in the table for which you want to implement validation. This action will open the "Field Properties" pane at the bottom of the screen.
  3. Define the validation rule. In the "Field Properties" pane, click on the row labeled "Validation Rule". Here, you can type in the rule you want to enforce. For instance, if you wish to ensure a numerical field always receives a value greater than 4, your validation rule would be ">4".
  4. Establish validation text. To help guide users, you can provide a message that will be displayed when the validation rule is violated. This is done in the "Validation Text" row in the "Field Properties" pane. A message corresponding to the previous example might be, "Please enter a value greater than 4."
  5. Save your changes. After inputting your validation rule and validation text, save the changes to the table. Access will now enforce these validation rules anytime data is entered into this field.

Examples of common Validation Rules:

Here are some examples of common validation rules:
  1. To enforce an exact number of characters for a text field: If you want entries in a text field to be exactly five characters, your validation rule would be "LEN([FieldName])=5". The corresponding validation text might be, "Please enter exactly five characters."
  2. To enforce date range constraints: If you want a date field to accept dates in the future only, your validation rule would be "[FieldName]>Date()". The corresponding validation text could be, "Please enter a future date."

Remember that validation rules in Access use a syntax akin to SQL, and they're not case sensitive. Use of logical operators like And, Or, and Not can combine conditions to form complex rules. Test your validation rules thoroughly to ensure they work as expected and maintain your database's data integrity.
Validation rules prevent bad data being saved in your table. Basically, they look like criteria in a query. You can create a rule for a field (lower pane of table design), or for the table (in the Properties box in table design.) Use the table's rule to compare fields. There is one trap to avoid. In some versions of Access, you will not be able to leave the field blank once you add the validation rule, i.e. you must enter something that satisfies the rule. If you need to be able to leave the field blank, add OR Is Null to your rule. Some versions accept Nulls anyway, but we recommend you make it explicit for clarity and consistency.