Access: Create validity rule - how it works
Access gives you the ability to automatically check values in fields. This practical tip shows you how to create a validity rule and not even allow incorrect entries.
Validity rule in Access for tables or forms
If you first define the rules in a table, these entries are automatically adopted when creating forms. The following instructions work with all Access versions from 2010.
- Open the desired table in the design view.
- Now click on the field name to which you want to assign a validity rule.
- In the lower area you will see the "General" tab. There you will find the entry "Validity rule".
- If you do not want to use a validity rule in principle, you can also create it for individual forms.
- To do this, open the corresponding form and switch to the design view.
- With the right mouse button click on the form field, which should receive a validity rule.
- Select the "Properties" entry in the context menu and then navigate to the "Data" tab.
Create a validation rule in Access
In order to be able to use validity rules sensibly, you should already know how to create expressions in Access.
- To create a validity rule, you can either enter your own rule or start the expression editor by clicking the button with the three dots.
- The expressions of the validation rules do not use any special syntax.
- This means that you can also set complex rules for fields.
- You cannot use a validity rule for the data types "AutoWert", "OLE-Objekt" and "Anlage".
Input examples for validity rules
This section provides a list of commonly used validation rules.
- To check an email address use the following syntax: ((Like "* ? *.? *") And (Not Like "* [, ;] *")). This means that the email address must contain at least one character, a subsequent "@" symbol, at least one further character, a period and another character. So allowed. It is not possible to enter or, com.
- With the rule "Is not zero" you determine that an entry must be made in the selected field.
- By entering> = # 01.01.2016 # you ensure that the date entered in the field is later than or equal to January 01, 2016.
- If the entry in a field should be zero or larger, enter> = 0.
- If only certain values should be allowed in the field, you can enter them in brackets: IN ("Germany", "Austria", "Switzerland").
- With> = 10 AND <= 99 you determine that entries may only be between 10 and 99.
- Extra tip: If an incorrect entry is made, you can create your own error message instead of the cryptic Access system message. Under the "Validity rule" field, you will see the "Validation message" entry. Enter the desired text here. If, for example, the user entered an incorrect email address, the message could look like this: You used an invalid email address. Please enter an address in the format [email protected] .