Access number validation rule examples

So far we have looked at validation rules that validate text. Let's now have a look at some examples of validation rules for numbers.

Number validation rule examples

The examples below apply to Number fields. The =, > and < symbols are called arithmetic operators. They are used to test numbers.

Type Example Explanation
Equal to = 32 Value must be equal to 32
Greater than or equal to >= 32 Value must be greater than or equal to 32
Greater than > 32 Value must be greater than 32
Smaller than or equal to <= 32 Value must be smaller than or equal to 32
Not equal to <> 32 Value must not be equal to 32
Between Between 0 and 32 Value must be between 0 and 32

If you have basic mathematics skills, validating numbers will be a simple task for you.

Positive numbers only example

A common constraint on Number field is to allow positive numbers only. The validation rule for this constraint is simple.

> 0

Numbers only example

When you try to type text into a Number field, Access will complain automatically.

Numbers only error

You can however check if the value of a Text field contains numbers only.

Not Like "*[!0-9]*"

This validation rule validates a Text value that conatins only numbers that are in the 0-9 (0,1,2,3...9) collection. The Not Like expression tests if values are not in the collection that follows. The character collection itself is a negative collection, because it starts with an exclamation mark (!). The Not Like statement and the ! operator together create an expression that says "validate any character that is NOT NOT inthis collection", or, in other words "validate any character that IS in the collection".