Examples of field validation rules (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

ValidationRule setting ValidationText setting
<>0 Please enter a nonzero value.
0 or >100 Value must be either 0 or over 100.
<#1/1/2000# Enter a date before 2000.
>=#1/1/2000#
and <#1/1/2001#
Date must be in 2000.
StrComp(UCase([LastName]),
[LastName],0) = 0
Data in the LastName field must be uppercase.

You can use also wildcard characters in a field validation rule. Which wildcard characters you use depends on the ANSI SQL query mode (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.) of the Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.).

The following example works in an Access database that uses Microsoft Jet SQL syntax.

ValidationRule setting ValidationText setting
Like "K???" Value must be four characters beginning with the letter K.

The following example works in an Access database that uses Microsoft SQL Server-compatible syntax (ANSI-92).

ValidationRule setting ValidationText setting
Alike "K___" Value must be four characters beginning with the letter K.
 
 
Applies to:
Access 2003