Tables & Queries:

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

Access has several features that can help you avoid certain kinds of data entry mistakes. You have already seen Input Masks, which restrict what kind of characters and how many you can enter.

You can also validate your data as you leave a field or record, according to validation rules that you create.

Examples below are from LanguageArtsClasses.mdb.

Validate a Field

Table Design View: Language Arts - Scores table - Validation rule and Validation textOne of the properties for a field in the Table Design View is Validation Rule. When you are entering or editing data, Access won't let you leave the field until the value fits the validation rule. A rule for a field cannot use values in other fields, so this rule cannot compare this field to another field.

If the field's value does not meet the rule, a message box will display the Validation Text from the field's properties. A well-written message will tell the user exactly what kind of value is acceptable.

If there is a validation rule for a field, the field cannot be Null, unless the rule itself allows it, like the first example below.

Examples of Validation Rules:  

Rule Means
<=110 Or Is Null The value is less than or equal to 110 or Null.
<>0 The value is non-zero amount.
>5  The value larger than 5.
<#1/1/2005#  The date is earlier than January 1, 2005
Is Not Null   There must be a value in the field.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing Tables Arrow: subtopic open
    Icon: StepDesign Tables
    Icon: StepFormat Fields
    Icon: StepIndexes
    Icon: StepValidation
    Icon: StepRedesign Table
    Icon: StepDocument a Table
    Designing QueriesTo subtopics
    ExercisesTo subtopics

Project 4: Forms & Reports


Other Ways to Restrict Field Values

Two other properties can also restrict a field.

Required property:  Yes or No. If you choose Yes, then you must enter a value in the field.

Allow Zero Length property: Yes or No. If Yes, then you are allowed to leave the field blank with a zero-length string. This is not the same as a value of Null. Confusing!

   What do they mean? Blank, null, & zero-length string

Blank: A field with no value showing looks blank and empty, like nothing is there. In fact, there are two different kinds of blanks - Null and Zero-length string.

Null: A Null field has no data. It is the same as saying "I have not entered a value for this field" or "I do not know if the value exists".

Example - Null value: You do not know whether or not a customer has a cell phone. The CellNumber field should be Null.

Zero-length string: The name Kathy is a string with length 5 because there are 5 characters in the text string.  "" (which is two double quote marks) is a string with 0 characters, a zero-length string. The double quote marks tell Access to start and stop a string of characters, but there are no actual characters in this string. Strange concept, for sure!!

If you type two double quote marks into a cell with nothing between them, like "", Access interprets this zero-length string as meaning "I know that this field does not have a value."

Once you leave the field, the quotes disappear. The cell looks blank again.

Example - Zero-length string value: You know that a customer does not have a middle name. The field MiddleName should contain "", a zero-length string. 

Validate a Record

Table Properties - Validation rule and Validation textIn the Properties for the table, you can set a validation rule that applies to the record as a whole. You cannot save the record until the values meet this rule. A rule for records can use the values from several fields, often to compare one to another.

How to show Table Properties: Click the Properties button Button: Properties while you are in Table Design View. This button is a toggle, hiding and showing the properties dialog.
Examples of Table Validation Rules:

Rule Means
[ShippingDate] > [OrderDate] + 14 The shipping date must be at least 14 days (two weeks) later than the date of the order
>=#1/1/2005# and <#1/1/2006# The date must be in the year 2005.

Other Validation Rules

While we are talking about validation, let's go ahead and mention some other ways to validate your data for a form or report.

Control:  In the Design View for a form or report, you can create a validation rule for a control. The validation rules for the field and for the table still apply. You must be careful that all of the rules work together correctly.

Macro or event procedure: Forms and reports can run little bits of programming as macros or as event procedures.  These little programs are often executed when a report is formatted or in response to an event, like clicking a button or saving a record. They might involve validating data.

Process of Validating

A particular field can easily wind up with several different rules that it must satisfy when it is used on a form or report. The order in which these rules are checked can sometimes be important.

Order of Validation:

  1. Macro or event procedure in a form or report
  2. Control on a form or report:  ValidationRule property
  3. Field's own properties:  ValidationRule, Required, and AllowZeroLength
  4. Table's property:  ValidationRule (This property performs validation on records)

Test Validation Rules

When you add validation rules after there is already data in a table, it would be nice to check that the existing data abides by the rules before you save the table.

In the Table Design View, if you right click on the title bar, one of the choices is  Test Validation Rules . Microsoft Access will test the rules in turn and let  you know if existing data violates a validation rule or the Required or AllowZeroLength settings. Access will NOT tell you which record or how many records violated the rule.

Icon: Step-by-Step 

Step-by-Step: Field & Record Validation

 Icon: Step-by-Step

What you will learn:

to set the Validation Rule for a field
to set the Validation Text for a field
to understand warning messages about breaking the rules
to set a Validation Rule and Validation Text for the table
to test the effect of validation rules
to add records after rules were created
to delete a field from datasheet view
to view subdatasheets

Start with:  Class disk, Projects database open.

You will add a new field to the Staff table and create a validation rule for it.

Create Field Validation Rule and Validation Text

  1. Design View: Staff - Date Hired, with validation rule and textIf necessary, open the Staff table in Design View.

  2. Click in the first blank row and type a new field name, DateHired.

  3. Choose the Date/Time type and accept the defaults.

  4. In the row for Validation Rule, type >#9/1/1994#
    This tells Access that the date must be later than Sept. 1, 1994, which is the founding date for World Travel Inc.

  5. In the row for Validation Text, type The DateHired must be later than Sept. 1, 1994.

Save Table After Validation Rule Created

  1.  Icon: Save Save the table.
    A message box appears warning you that the rules for data integrity have changed.

    Message: Data integrity rules have changed; existing data may not be valid for the new rules.

    Sometime you want only the new data to fit the rule. Sometimes you need all of the existing records to fit. It is important to know what you want to do!

    TipWhen you make this kind of change after creating many records, it can take a long time for Access to verify how the data fits with the new rules. It is better to figure out what needs to be validated when you design the table to start with!

  2. Click on Yes. Since there is only one record, it won't take long for Access to do the check!
    Another message box appears telling you that existing data violates the rule.

    Message: Existing data violates the new setting...

    Don't panic! You have not entered any data into the new field yet!
    Therefore, the new field is Null, which is not a date at all! There isn't really a problem yet.

  3. Click on Yes to keep the new setting and continue testing.
    Note: This message will appear once for each rule that was violated, not for each record.

  4. Icon: Datasheet Switch to Datasheet View.
  5. For the first record, enter the date August 31, 1994 as 08/31/94, which is earlier than the rule requires.
  6. Icon: Save Save value (mouse method):
    in the another field to save the value you just entered.
    Message: The DateHired must be later than Sept. 1, 1994A message box pops up and displays the Validation Text that you entered for the DateHired field.
  7. Click on OK.
  8. Change the date to 09/02/94.
  9. Icon: Save Save record (menu method):
    From the menu select Records | Save Record. No messages this time!
    This command saves the record but leaves your cursor in the cell.
    Icon: Keyboard Keyboard shortcut to save a record is SHIFT + ENTER.

Create Table Validation Rule and Validation Text

The table has properties of its own, including Validation Rule and Validation Text properties. The rule applies to a record rather than to an individual field and can use more than one field.

  1. Icon: Design Switch to the Design View for the Staff table.
  2. Dialog: Table PropertiesOn the toolbar, click Button: Properties the Properties button.
    A dialog appears for the properties of this table. The title bar does not give the name of the table! It applies to the active table, so you must pay attention to which one that is.
    Icon: TroubleNo Properties dialog appears:
          The Properties button is a toggle button to show or hide the dialog. The dialog was already open so your click closed it.
    Solution: Click the button again.
    Next you will create a validation rule, but mis-type part so that you can see how smart Access is, and how not-quite-helpful Access is!
  3. In the Validation Rule box, type [DateHired]>[BirthDate>
    This expression is supposed to tell Access that the value in the field DateHired must be larger (later) than the value in the field BirthDate, but you typed a > instead of ].
  4. Click in the Validation Text row, which makes Access try to save your new rule.
    A message box appears that says you are missing something. This is helpful but not as helpful as showing you which character is missing! Access must know which, but it won't tell!

    Message: The expression you entered is missing a closing parenthesis, bracket (]), or vertical bar (|).

  5. Click on OK and correct the error by replacing the last > with a square bracket ].
  6. In the Validation Text box, type DateHired must be later than BirthDate.

Test the Rule

You should always test your rules. Sometimes rules combine in ways that you did not expect!

  • Test 1:  Data that fits the rules
  • Test 2:  Data that does NOT fit.
  • Test 3:  Values at the edges.
    Example: Should the rule have been >= (greater than or equal to) instead of just >??
  1. Icon: Datasheet Switch to Datasheet View.
    Once again you must save the table and let Access verify the data.

    Test 1: Data that fits: You have already entered data that fit the rule and the rule worked as expected.
  2. Test 2: Data that does not fit
    the BirthDate value to 10/1/97, which is after the DateHired.
  3. TAB out to the next field. There is no warning message! There is no validation rule for the BirthDate field. Your new rule is for the record so you will have to save the record, not just the value.
  4. Message: The DateHired must be later than the BirthDate.Icon: Save Save the record with the key combo SHIFT + ENTER.
    Now the warning message appears!
  5. Click OK.
  6. Test 3: Change the BirthDate value to 09/02/94.
    [Remember that this field uses an input mask so you must enter the value with 2 digits for month, day, and year.]
  7. Icon: Save Save the record again.
    The warning message appears again.
  8. Icon: Save Change the BirthDate back to 03/14/54 and save the record.
    No messages this time!

Add Records

You need to add records to the Staff table. You must at least have records for the people already included in the ProjectStaff table. Of course there would be more people than this in a real company.

  1. Enter the data below in the Staff table. The EmployeeID is an autonumber field, so you won't actually type that in.
    Some fields in the table will be blank for all records and some will be blank for certain records. If you mis-typed in a field with a validation rule, pay attention to the message that appears.
EmployeeID 2 3 4 5
First Name Hector William Wilhelm Mark
Middle Name   Eric G Samuel
Last Name Chavez Gardner Heinz Friedman
Title Agent Agent Agent Agent
Email Name hchavez bgardner wheinz markf
Address Primero 3344 Segundo 1415 Circulo 440 11 Piney Ridge
City Buenos Aires Buenos Aires Buenos Aires San Diego
State       CA
Postal Code 1123 1124 1123 99800
Country Argentina Argentina Argentina USA
Home Phone 999-0011 555-2253 555-6631 999-555-1123
Birthdate 01/25/65 06/19/71 11/09/55 09/30/72
Spouse Name Carla Chavez Sofia Gardner Charlotte Heinz Helena Friedman
Emrgcy Contact Name Carla Chavez Sofia Gardner Charlotte Heinz Helena Friedman
Emrgcy Contact Phone 999-0011 (   ) 555-2533 (   ) 555-6631 (999) 555-7766
Notes       Esp. informed on golf packages
Date Hired 04/15/2000 2/3/2004 8/9/1995 5/25/2004


EmployeeID 6 7 8 9
First Name Heidi Sheila Jorge Juanita
Middle Name Grace Harriet Pablo  
Last Name Fuller Landers Martinez Gonzales
Title Secretary Branch Manager Agent Branch Manager
Email Name heidifuller slanders jmartinez jgonzales
Address 6633 Harding Road 42 West Beach Drive 1442 West Columbia Dr. Primero 5843
City Nashville Sydney Franklin Buenos Aires
State TN NSW TN  
Postal Code 37456 2000 45672 1123
Country USA Australia USA Argentina
Home Phone 615-555-3476 5555-6666 615-555-9977 555-1487
Birthdate 4/12/1980 03/23/82 12/23/85 05/13/70
Spouse Name   Paul Landers Flora Martinez  
Emrgcy Contact Name Margaret Fuller Paul Landers Flora Martinez Jorge Gonzales
Emrgcy Contact Phone 615-555-4466 5555-6666 615-555-9977 555-8879
Date Hired 12/5/2002 2/15/2003 2/15/2003 01/05/95

After all of this typing directly in the table, you can see why another method was invented. Using a form would make this easier in many ways. That is covered in the next project, Forms and Reports.