Contents

List of Access validation rule examples

In this validation rule tutorial we talked about validation rules for text, numbers, dates and tables. The listings below summarize the examples explained in this tutorial.
 

Text validation rule examples

The following validation rule examples apply to text fields.

Example Matches...
Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]" A number consisting of 8 digits
Like "########" A number consisting of 8 digits
Like "????" 4 characters
Like "K*" A text starting with the letter K or k
Is Not Null Any value but null/empty
Not Null And Like "K*i*e" A text staring with the letter K/k, followed by zero or more characters (*), followed by a i/I, followed by zero or more characters (*), followed by an e. Null/empty is not allowed.
Like "James Joyce" Matches "James Joyce"
Like "a?b?c" Matches an a, followed by any single character, followed by a b, followed by any single character, followed by a c
Like "*@*.*" A very basic email validation rule. This matches any number of characters (including no characters), followed by an @, followed by any number of characters (uncluding no characters), followed by a ., followed by any number of characters (including no characters).
Like "??*@??*.??*" A basic email validation rule. Matches at least two characters, followed by an @ sign, followed by at least two characters, followed by a ., followed by at least two characters.
Like "bi[nd]" Matches "bin" or "bid"
Like "??[ab]*" And Not Like "*[q]*" Matches two characters, followed by an a or a b, followed by any number of characters and NONE of the characters can be a q.
Like "??[ab]*" And Not Like "*[!qab]*" Matches two characters, followed by an a or b, followed by any number of characters and ALL of the characters must be q, a or b.
Like "??#*" And Not Like "*[!0-9]*" Matches two characters, followed by a digit, followed by any number of characters and ALL of the characters must be numbers.
Like "?*@?*.??*" And Not Like "*[!a-z@=.^_$%!#&'`{|}*?~/-]*" More advanced email validation. Matches the pattern "?*@?*.??" if ALL characters are part of the the [a-z@=.^_$%!#&'`{|}*?~/-] character collection.
Not Like "*[!a-z]*" Letters only
Not Like "*[!a-z ]*" Letters and spaces only
Not Like "*[!0-9a-z]*" Letters and numbers only
Not Like "*[!0-9a-z -]*" Letters, numbers, spaces and hyphens (-) only
"Jack" Or "John" Or "Marc" Jack, John or Marc

Number validation rule examples

The following validation rule examples apply to Number fields.

Example Matches...
> 0 Positive numbers only, excluding 0
>= 0  Positive numbers only, including 0
>= 0 And <= 100 Between 0 and 100, including 0 and 100
Between 0 And 100 Between 0 and 100, including 0 and 100
In(1,2,3,4,5) 1,2,3,4 or 5
1 Or 2 Or 3 Or 4 Or 5 1,2,3,4 or 5

Date validation rule examples

The following validation rile examples apply to dates. Note that the way dates are written depends on your locale. The examples below use a European date format (dd-mm-yyyy).

Example Matches...
>#28/2/1990# Dates after 28 februari 1990
Between #28/2/1990# And #5/3/1990# Dates between 28 februari and 5 march 1980
>= #28/2/1980# And <= #5/3/1990# Dates between 28 februari and 5 march 1980
> #28/2/1990 13:45# Date/Time after 28 februari 1990 13:45
> Date() Dates after todays date
> Now() Date/Time after the current Date/Time