Advertisements

Restrict data input by using validation rules

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

You can investigate or validate data in Access desktop databases as you type it by using validation rules. You can use the expression builder to aid you format the rule properly. Validation rules can be implemented in either table design or table datasheet view. There are three types of validation rules in Access:

1. Field Validation Rule –  You can use a field validation rule to detail a criterion that all valid field values must meet. You should not have to distinguish the current field as a part of the rule unless you are using the field in a function. Restrictions on types of characters to be put in a field may be easier to do with an Input Mask. For example, a date field might have a validation rule that disallows values in the past.

Quick examples:

Disallow date values in the past: >=Date()

Generally accepted email format: Is Null OR ((Like “*?@?*.?*”) AND (Not Like “*[ ,;]*”))

Number less than or qual to five: <=5

Currency field can’t be negative: >=0

Restrict character length in string: Len([StringFieldName])<100

Field validation rule displayed in Access table designer

2. Record Validation Rule  – You can apply a record validation rule to clarify a condition that all valid records must satisfy. You can compare values across different fields using a record validation rule. For example, a record with two date fields might require that values of one field always precede values of the other field (e.g., StartDate is before EndDate).

Quick examples:

Check the end date doesn’t come before the start date: [End Date]>=[Start Date]

State a required date that occurs no more than 30 days after the order date: [RequiredDate]<=[OrderDate]+30

A table valdation rule in the Access table designer.

3. Validation on a form  – You can use the Validation Rule property of a control on a form to define a criterion that all values input to that control must meet. The Validation Rule control property functions like a field validation rule. Typically, you use a form validation rule as a substitute for a field validation rule if the rule was specific only to that form and not to the table no matter where it was used.

In this article

Overview

This article explains how to use validation rules and validation text in table fields and form controls. A validation rule is one way to constrict input in a table field or a control (such as a text box) on a form. Validation text enable you to supply a message to guide users who input data that is not valid.

When data is entered, Access scans to view whether the input breaches a validation rule if so, the input is not accepted, and Access displays a message.

Access provides a number of ways to restrict input:

  • Data types –   Every table field contains a data type that constrains what users can state. For example, a Date/Time field accepts only dates and times, a Currency field accepts only monetary data, and so on.

  • Field properties –  Some field properties limit data input. For example, the Field Size property of a field restricts input by limiting the amount of data. You can even use the Validation Rule property to request specific values, and the Validation Text property to inform your users to any mistakes. For example, typing a rule such as >100 And <1000 in the Validation Rule property forces users to enter values between 100 and 1,000. A rule such as [EndDate]>=[StartDate] coerces users to state an ending date that happens on or after a starting date. Inputting text such as “Enter values between 100 and 1,000” or “Enter an ending date on or after the start date” in the Validation Text property alerts users when they have made a mistake and how to fix the error.
  • Input masks  – You can use an input mask to validate data by prompting users to type values in a specific way. For example, an input mask can force users to enter dates in a European format, such as 2007.04.14.

You can use these methods of validating data alone or in tandem with each other. Data types are mandatory, and offer the most basic type of data validation.

For more information about data types, field sizes, and input masks, see the article Introduction to data types and field properties.

Types of validation rules

You can create two basic types of validation rules:

  • Field validation rules –  Use a field validation rule to check the value that you place in a field once you exit the field. For example, suppose you have a Date field, and you enter >=#01/01/2010# in the Validation Rule property of that field. Your rule now requires users to enter dates on or after January 1, 2010. If you enter a date earlier than 2010 and then try to place the focus on another field, Access prevents you from leaving the current field until you fix the problem.
  • Record validation rules –  Use a record validation rule to dictate specifically when you can save a record (a row in a table). Unlike a field validation rule, a record validation rule assigns other fields in the same table. You produce record validation rules after you must check the values in one field relative to the values in another. For example, suppose your business requires you to ship products within 30 days and, if you don’t ship within that time, you must refund part of the purchase price to your customer. You can determine a record validation rule such as [RequiredDate]<=[OrderDate]+30 to ensure that someone doesn’t enter a ship date (the value in the RequiredDate field) too far into the future.

If the syntax for validation rules appears puzzling, see the section What you can put in a validation rule for an explanation of the syntax and some example validation rules.

Where you can use validation rules

You can set validation rules for table fields and for controls on forms. After you define rules for tables, those rules come into play once you import data. To insert validation rules to a table, you access the table and use commands on the Fields tab of the ribbon. To insert validation rules to a form, you open the form in Layout view and create rules to the properties of individual controls.

The steps in the section Add a validation rule to a table explain how to add validation rules to table fields. The steps in the section Add a validation rule to a control on a form, later in this article, explain how to apply rules to the properties in individual controls.

What you can put in a validation rule

Your validation rules can harbour expressions — functions that yield a single value. You can utilise an expression to run calculations, exploit characters, or experiment data. A validation rule expression tests data. For example, an expression can check for one of a series of values, such as “Tokyo” Or “Moscow” Or “Paris” Or “Helsinki”. Expressions can also conduct mathematical operations. For example, the expression <100 forces users to enter values that are less than 100. The expression ([OrderDate] – [ShipDate]) calculates the number of days that elapsed between the time an order was placed and the time it shipped.

For more information about expressions, see the article Build an expression.

Top of Page

Add a validation rule to a table

You can insert a field validation rule and/or a record validation rule. A field validation rule reviews input to a field, and is relayed once the focus exits the field. A record validation rule evaluates input to one or greater fields is transferred after the focus emerges from the record. Typically, a record validation rule benchmarks the values of two or more fields.

Notes: The following field types do not support validation rules:

  • AutoNumber
  • OLE Object
  • Attachment
  • ReplicationID

Create a field validation rule

  1. Choose your sought field to validate.
  1. On the Fields tab, in the Field Validation group, select Validation, and then choose Field Validation Rule.
  1. Apply the Expression Builder to define the rule. For more information about using the Expression Builder, see the article Use the Expression Builder.

Create a message to display for field input that is not valid

  1. Pick the field that needs a message for input that is not valid. The field should already be equipped with a validation rule.
  2. On the Fields tab, in the Field Validation group, pick Validation, and then press Field Validation Message.
  3. Writer a suitable message. For example, if the validation rule is >10, the message could be “Enter a value that is less than 10.”

For some examples of field validation rules and messages, see the section Validation rule reference.

Create a record validation rule

  1. Access the table for which you want to validate records.
  2. On the Fields tab, in the Field Validation group, choose Validation, and then pick Record Validation Rule.
  3. Use the Expression Builder to implement the rule. For more information about using the Expression Builder, see the article Use the Expression Builder.

Create a message to display for record input that is not valid

  1. Launch the table that requires a message for input that is not valid. The table should already own a record validation rule.
  2. On the Fields tab, in the Field Validation group, choose Validation, and then tap Record Validation Message.
  3. Type a relevant message. For example, if the validation rule is [StartDate]<[EndDate], themessage might be “StartDate must precede EndDate.”

Top of Page

Test existing data against a new validation rule

If you insert a validation rule to an existing table, you may want to rehearse the rule to check whether any existing data is not valid.

  1. Access the table that you want to test in Design View. On the Design tab, in the Tools group, select Test Validation Rules.

  1. Press Yes to dismiss the alert message and start the test.

  1. If prompted to save your table, choose Yes.

  1. You may notice a range of other alert messages as you progress. Read the instructions in each message, and then confirm Yes or No, as necessary, to finish or halt the testing.

Top of Page

Add a validation rule to a control on a form

You can apply the Validation Rule property and the Validation Text property of a form control to validate data that is input to that control and to aid users who input data that is invalid.

Tip: If you create a form automatically from a table by using one of the Form commands on the ribbon, any validation for fields in the underlying table are inherited by the corresponding controls on the form.

A control can inherit a different validation rule from the table field to which the control is bound. This is helpful if you want the form to be more controlled than the table. The form rule is set, and then the table rule is enforced. If the table is more bounded than the form, the rule configured for the table field takes priority. If the rules are independent in their own right, they impede you from typing any data at all.

For example, suppose you apply the following rule to a date field in a table:

<#01/01/2010#

But you then apply this rule to the form control that is bound to the date field:

>=#01/01/2010#

The date field now requires values earlier than the year 2010, but the form control requires dates have that year or later, thus preventing you from entering any data at all.

Create a validation rule for a control

  1. Right-click your preferred form to edit, and then pick Layout View.
  1. Right-click your desired control to update, and then choose Properties to access the property sheet for the control.
  1. Select the All tab, and then type your validation rule in the Validation Rule property box.

Tip: Select the Build button to start the Expression Builder.For more information about using the Expression Builder, see the article Use the Expression Builder.

  1. Input a message in the Validation Text property box.

Top of Page

Validation rule reference

Validation rules use Access expression syntax. For more information about expressions, see the article Introduction to expressions.

Validation rule and validation text examples

Validation ruleValidation text
<>0Type a nonzero value.
>=0Value must be zero or greater.-or-You must type a positive number.
0 or >100Value must be either 0 or greater than 100.
BETWEEN 0 AND 1Type a value with a percent sign. (For use with a field that stores number values as percentages).
<#01/01/2007#Type a date before 2007.
>=#01/01/2007# AND <#01/01/2008#Date must occur in 2007.
<Date()Birth date cannot be in the future.
StrComp(UCase([LastName]),
[LastName],0) = 0
Data in a field named LastName must be uppercase.
>=Int(Now())Type today’s date.
M Or FType M for male or F for female.
LIKE “[A-Z]*@[A-Z].com” OR “[A-Z]*@[A-Z].net” OR “[A-Z]*@[A-Z].org”Type a valid .com, .net, or .org e-mail address.
[RequiredDate]<=[OrderDate]+30Type a required date that occurs no more than 30 days after the order date.
[EndDate]>=[StartDate]Type an ending date on or after the start date.

Syntax examples for common validation rule operators

OperatorFunctionExample
NOTTests for converse values. Use before any comparison operator except IS NOT NULL.NOT > 10 (the same as <=10).
INTests for values equal to existing members in a list. Comparison value must be a comma-separated list enclosed in parentheses.IN (“Tokyo”,”Paris”,”Moscow”)
BETWEENTests for a range of values. You must use two comparison values — low and high — and you must separate those values with the AND separator.BETWEEN 100 AND 1000 (the same as >=100 AND <=1000)
LIKEMatches pattern strings in Text and Memo fields.LIKE “Geo*”
IS NOT NULLForces users to type values in the field. This is the same as setting the Required field property to Yes. However, when you enable the Required property and a user fails to enter a value, Access displays a somewhat unfriendly error message. Typically, your database is easier to use if you use IS NOT NULL and type a friendly message in the Validation Text property.IS NOT NULL
ANDSpecifies that all parts of the validation rule must be true.>= #01/01/2007# AND <=#03/06/2008#Note: You can also use AND to combine validation rules. For example: NOT “UK” AND LIKE “U*”.
ORSpecifies that some but not all parts of the validation rule must be true.January OR February
<Less than.
<=Less than or equal to.
>Greater than.
>=Greater than or equal to.
=Equal to.
<>Not equal to.

Using wildcard characters in validation rules

You can utilise wildcard characters in your validation rules. Always remember that Access complements two groups of wildcard characters: ANSI-89 and ANSI-92. Every standard applies a various set of wildcard characters.

As an established setting, all .accdb and .mdb files use the ANSI-89 standard.

You can modify the ANSI standard for a database to ANSI-92 by using the following procedure:

  1. On the File tab, press Options.
  1. In the Access Options dialogue box, select Object Designers.
  1. In the Query design section, under SQL Server Compatible Syntax (ANSI-92), click This database.

For further information about using wildcard characters and the ANSI standards for SQL, read the article Access wildcard character reference.

Top of Page

Advertisements

Leave a Reply

%d bloggers like this: