Access 2007: Data Validation

Access 2007: Data Validation

One of the ways to ensure the proper information is being entered into your forms and tables is by using field data validation.

With field data validation, you can limit or control what a user can enter in a table field or a control. For example, you may have a table tracking employee pay rates. To ensure correct data entry, you could create a field data validation rule that specifies the amount entered in the hourly pay rate field must fall between $7.25 and $25.50. In Access, it would appear as:

Between 7.25 AND 25.50

One of the nice things about using Validation Rules is the ability to display an information error dialog box to inform your user when they’ve broken the Validation Rule. And, it’s as easy as entering the text you want the user to see when the error message is displayed. In Access, you would enter:

Please enter a figure between $7.25 and $25.50. Thank you.

…to display…

Figure 10. Validation Text Message Box

Validating Data in a Field

Open a table in Design View

Select the field that you want to add the Data Validation Rule to

Click in the Field Properties section (or, press [F6])

Click in the Validation Rule property box

Type your validation rule

Click in the Validation Text property box

Type your validation message

Save the Table

Switch to Datasheet View

Test your validation rule

Sample Validation Rules and Text

Validation rule

Validation text

<>0

Enter a nonzero value

>=0

Value must be zero or greater

0 or >100

Value must be either 0 or greater than 100

BETWEEN 0 AND 1

Enter a value with a percent sign. (For use with a field that stores number values as percentages)

<#01/01/2007#

Enter a date before 2007

>=#01/01/2007# AND <#01/01/2008#

Date must occur in 2007

<Date() Birth

date cannot be in the future

StrComp(UCase([LastName]),[LastName],0) = 0

Data in a field named LastName must be uppercase

>=Int(Now())

Enter today’s date

M Or F

Enter M for male or F for female

LIKE “[A-Z]*@[A-Z].com” OR “[A-Z]*@[A-Z].net” OR “[A-Z]*@[A-Z].org”

Enter a valid .com, .net, or .org e-mail address

[RequiredDate]<=[OrderDate]+30

Enter a required date that occurs no more than 30 days after the order date

[EndDate]>=[StartDate]

Enter an ending date on or after the start date

Expression Rules

Surround the names of table fields with square brackets, for example: [RequiredDate]<=[OrderDate]+30.

Surround dates with pound signs (#), like so: <#01/01/2007#

Surround text with double quotation marks, for example: IN (“Tokyo”,“Paris”,“Moscow”).

Common Arithmetic Operators

Operator

Function

Example

NOT

Tests for converse values.

NOT > 10 (the same as <=10).

IN

Tests for values equal to existing members in a list.

IN (“Tokyo”,“Paris”,“Moscow”)

BETWEEN

Tests for a range of values.

BETWEEN 100 AND 1000 (the same as >=100 AND <=1000)

LIKE

Matches pattern strings in Text and Memo fields.

LIKE “Geo*”

IS NOT NULL

Forces users to enter values in the field. This is the same as setting the Required field property to Yes.

IS NOT NULL

AND

Specifies that all the data that you enter must be true or fall within limits that you specify.