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.
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
Enter a nonzero value
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)
Enter a date before 2007
>=#01/01/2007# AND <#01/01/2008#
Date must occur in 2007
date cannot be in the future
StrComp(UCase([LastName]),[LastName],0) = 0
Data in a field named LastName must be uppercase
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
Enter a required date that occurs no more than 30 days after the order date
Enter an ending date on or after the start date
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
Tests for converse values.
NOT > 10 (the same as <=10).
Tests for values equal to existing members in a list.
Tests for a range of values.
BETWEEN 100 AND 1000 (the same as >=100 AND <=1000)
Matches pattern strings in Text and Memo fields.
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
Specifies that all the data that you enter must be true or fall within limits that you specify.
>= #01/01/2007# AND <=#03/06/2008#
Specifies that one or more pieces of data can be true.
January OR February
Less than or equal to
Greater than or equal to
Not equal to