The National Orthodox School / Jordan

Restrict data input by using a validation rule

Expressions for Numbers

Comparison Sample Expression Description

Equal to = 42 The value must be 42.

Less than <100 The value must be less than 100.

Less than or equal to <=100 The value must be less than or equal to 100.

Greater than >0 The value must be greater than 0.

Greater than or equal to >= 0 The value must be greater than or equal to 0.

<> 42
Not equal to The value can be anything except 42.
Not 42

Greater than X and less The value must be greater than 10 and less than
>10 and <20
than Y 20. 10 and 20 are not included.

Between 0 and 100 The value must be 0, 100, or somewhere in

Between
>=0 and <=100 between.

Expressions for Dates and Times

Comparison Sample Expression Description

Less than <#30/1/2012# The date occurs before January 30, 2012.

Greater than >#30/1/2012# The date occurs after January 30, 2012.

Between #1/10/2013#
Between The date occurs on October 2010.
and #31/10/2013#

Greater than the current

>Date( ) The date occurs today or after.
date

Less than the current date <Date( ) The date occurs yesterday or before.

Greater than the current The date occurs today after the current
>Now()
date (and time) time, or any day in the future.

Less than the current date The date occurs today before the current
<Now()
(and time) time, or any day in the past.

Note: You have to write Full date.

Page 1/4 Wesam Abu Khader email: wesamict@hotmail.com Phone: +962796304364

 The National Orthodox School / Jordan

Expressions for Text

With text, validation lets you verify that a value starts with, ends with, or contains specific
characters. You perform all these tasks with the Like operator, which compares text to a
pattern.

Sample Expression Description

The asterisk (*) represents (zero or more) characters. Thus, the complete expression asks
Access to check that the value starts with R (or r), followed by a series of zero or more
characters.

Like "R*"
starts with The text must start with the letter R.

Like "*ed"
ends with The text must end with the letters ed.

Contains requires that number 5 appears

Like “*5*”
somewhere in a text field

You can use the wildcard (?) to match a single character, which is handy
if you know how long text (field Size) should be or where a certain letter should appear

eight-character product Like "?????0ZB" Entry must be 8 characters and end with
code that ends with 0ZB the characters "0ZB".

Five-character code that Entry must be 5 characters and begin with

Like "A????"
begins with A. the letter "A".

OR
You can use the or keyword to accept a value if it meets one, two or more conditions.

>1000 Or <-1000

Like "A???????" or Like "B???????"

“A” or “B” or “C” or “D” or “E”

“Amman” or “Aqaba”

Null = EMPTY (Blank)

If you must fill Same as setting the field's Required property, but lets you
Is Not Null
in the field create a custom message (in the Validation Text property.)

Page 2/4 Wesam Abu Khader email: wesamict@hotmail.com Phone: +962796304364

The National Orthodox School / Jordan

Validation Rule for

To do this ... Explanation
Fields
Same as setting the field's Required property, but
You must fill
Is Not Null lets you create a custom message (in the Validation
in the field
Text property.)
Positive
Is Null OR >= 0 Positive numbers only or empty
numbers only
No more than 100% is 1. Use 0 instead of -1 if negative
Between -1 And 1
100% percentages are not allowed.

Between 1000 And

Exactly 4 digits For Number fields.
9999

No more than 100% is 1. Use 0 instead of -1 if negative

Between -1 And 1
100% percentages are not allowed.
Not a future
<= Date()
date
Requires at least one character, @, at least one
Email address Like "*?@?*.?*"
character, dot, at least one character.
It is better to use a lookup table for the list, but this
Limit to specific "M" Or "F" may be useful for simple choices such as
choices Male/Female.
IN (1, 2, 4, 8) The IN operator may be simpler than several ORs.

ValidationText :

Use the ValidationText property to specify a message to be displayed to the user when
data is entered that violates a ValidationRule setting for a field.

Page 3/4 Wesam Abu Khader email: wesamict@hotmail.com Phone: +962796304364

The National Orthodox School / Jordan

Input Mask

You can use the Input Mask property to make data entry easier and to control the values
users can enter in a field.

Character Description
0 Digit (0 to 9, entry required, plus [+] and minus [–] signs not allowed).
9 Digit or space (entry not required, plus and minus signs not allowed).
L Letter (A to Z, entry required).
? Letter (A to Z, entry optional).
A Letter or digit (entry required).
a Letter or digit (entry optional).
& Any character or a space (entry required).
C Any character or a space (entry optional).
< Causes all characters to be converted to lowercase.
> Causes all characters to be converted to uppercase.
\ Causes the character that follows to be displayed as the literal character (for
example, \A is displayed as just A).

To do this ... Input Mask Sample Values

10 digits 0000000000 4567654345
AK2345
2 Letters followed by 3 digits LL0000
CD1234
1 Letter followed by Dash
L\-00 A-23
then 2 digits
A Phone Style \(000") "000\-0000 (206) 555-0248
Password Field Password *******

Note: You can Use the wizard button to set the Input Mask or Validation Rule.

Page 4/4 Wesam Abu Khader email: wesamict@hotmail.com Phone: +962796304364