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.

About Ben

Ben Beitler is a highly proficient software developer & consultant with over 19 years experience of database design and implementation of CRM management systems, bespoke applications using VBA (mainly Excel spreadsheets & Access databases). Currently he is also working as coach for individuals and small businesses who are looking to use the Internet to market their businesses focusing on SEO and traffic building to websites. He is a fellow member of the The Institute of IT Training which is the world's first professional body for IT training professionals which sets standards and code practices within the industry. Having successfully run several businesses, Ben currently provides training, consultancy and development in Access and Excel ranging from small to large companies including Ford, Toyota, HSBC, Barclays, PWC, Deloitte, Sage and many others. Even though he is based in the UK (in and around the London area), his services have been used as far as Australia consulting and developing systems with software specialists. Ben has created website resources for Microsoft Access database and Microsoft Excel spreadsheet which are free to use which collate news articles, tips 'n' tricks, video tutorials and support questions from the more popular websites around including Google, Yahoo and YouTube.
This entry was posted in Forms, MS Access, Tables and tagged , , , . Bookmark the permalink.

3 Responses to Microsoft Access Database: Creating a Validation Rule in an Access Table

  1. Ben says:

    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. Mary Rose Conquilla says:

    how to make formula in table
    example field column1 + field column2

  3. Ben Beitler says:

    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?

Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>