A validation rule is an expression that limits the values that can be entered in the field.

Examples of validation rules

Accept Or Reject will only allow “Accept” or “Reject“
>=0 number cannot be negative
>=Date() date cannot be in the past

Between Date() And DateAdd(“m”,6,Date()) date must be within the next six months

Like BN* text string must start with “BN”

List of operators that can be used for validation rules

OperatorFunctionExample
NotTests for converse values. Use before any comparison operator except IS NOT NULL.Not > 20
InValue must be in the specified list.In (“Sussex”,”Surrey”,”Kent”)
BetweenValue must be in the range specifiedBetween 0 And 10
LikeUse wildcard character * to define the pattern of a valid string valueLike “BN*” – must start with BN
Like “*BN*” – must contain BN
Like “*BN” – must end with BN
Is Not NullValue must be entered in the field. This is the same as setting the Required field property to Yes. The benefit of using a validation rule here is that you can specify validation text which is more user friendly than the default one for the required field property.Is Not Null
AndSpecifies that all parts of the validation rule must be true.>=0 And <=10
OrValue can be any of the values specifiedSussex Or Kent
<Less than.
<=Less than or equal to.
>Greater than.
>=Greater than or equal to.
=Equal to.
<>Not equal to.

Testing Validation Rules

You can test validation rules within a table by clicking on the Test Validation Rules button on the Ribbon’s Design tab.  This assumes you are in the table’s design view.

Validation Text

Use in conjunction with the validation rule to create an error message that appears when a user enters a prohibited value.

The validation text shown above would appear like this.

Posted by Blue Pecan Computer Training