1-866-245-5224 sales@keystonelearning.com

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

     
  1. Open a table in Design View
     
  2.  
  3. Select the field that you want to add the Data Validation Rule to
     
  4.  
  5. Click in the Field Properties section (or, press [F6])
     
  6.  
  7. Click in the Validation Rule property box
     
  8.  
  9. Type your validation rule
     
  10.  
  11. Click in the Validation Text property box
     
  12.  
  13. Type your validation message
     
  14.  
  15. Save the Table
     
  16.  
  17. Switch to Datasheet View
     
  18.  
  19. 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.

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

OR

Specifies that one or more pieces of data can be true.

January OR February

Comparison Operators

               

This Symbol…

Means…

<

Less than

<=

Less than or equal to

>

Greater than

>=

Greater than or equal to

=

Equal to

<>

Not equal to