Examples of field validation rules (MDB)

Microsoft Office Access 2003

Show All Show All

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 of the Microsoft Access database.

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.