Microsoft Access Database: Creating a Validation Rule in an Access Table

Take a look at this quick and clear explanation of how to set Validation Rules in Microsoft Access via a table.

The validation rules use the same logical expressions that can be found typically in criteria of a query and must return a logical value of either TRUE or FALSE in order to accept or reject the value and trigger an exception (validation text property). This simple property is easier to apply than attaching a macro or VBA code to an event which means learning VBA programming!

Additionally, you can set validation rules in a form too but remember this will only be available in the one form compared to a table which other forms inherit making it more globally available.

Finally, don’t confuse validation rules for field in a table which this video tutorial is about to validation rules for a record which is also available in a table.

I would love to hear from you by commenting below on how you use validation rules perhaps sharing some examples for other members to see and understand.

4 Replies to “Microsoft Access Database: Creating a Validation Rule in an Access Table”

  1. Examples of a validation rule:

    Accept only a positive value starting from 1 for entering quantity in a field:
    Validation Rule: >=1 Validation Text: “Make sure you enter a positive value starting from 1”

    To validate the date entered in a field is no older than 7 days ago.
    Validation Rule: >=Date-7 Validation Text: “Dates must not be older than 7 days ago”

    Which ones do you use?

    1. Hi,
      Creating a formula in a table was introduced from version 2010 using the calculated field.
      You can create formulae in other objects across all versions including the query, form, report and macros.
      To create a formula in a table for example, choose the ‘calcuated’ data type which opens an expression builder tool to enter or choose the formula.
      To create a formula to add two fields together would look something like [Field1] + [Field2] which appears the ‘Expression’ property of the new field.
      If you need further help, please comment back.
      Thanks, Ben

  2. Wonderful site. Lots of helpful info here. I’m sending it to a few buddies ans also sharing in delicious. And of course, thank you for your effort!

Leave a Reply

Your email address will not be published. Required fields are marked *