Jan's Access Tables & Queries: Validation

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!

Validate a Record

Table Properties - Validation rule and Validation text

In 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

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

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:

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 . 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.

Step-by-Step: Field & Record Validation

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: , 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

If necessary, open the Staff table in Design View.
Click in the first blank row and type a new field name, DateHired.
Choose the Date/Time type and accept the defaults.
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.
In the row for Validation Text, type The DateHired must be later than Sept. 1, 1994.

Save Table After Validation Rule Created

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

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!

When 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!
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.

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.
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.
Switch to Datasheet View.
For the first record, enter the date August 31, 1994 as 08/31/94, which is earlier than the rule requires.
Save value (mouse method):
Click in the another field to save the value you just entered.
A message box pops up and displays the Validation Text that you entered for the DateHired field.
Click on OK.
Change the date to 09/02/94.
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.
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.

Switch to the Design View for the Staff table.
On the toolbar, click 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.

No 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!
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 ].
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!
Click on OK and correct the error by replacing the last > with a square bracket ].
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 >??

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.
Test 2: Data that does not fit
Change the BirthDate value to 10/1/97, which is after the DateHired.
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.
Save the record with the key combo SHIFT + ENTER.
Now the warning message appears!
Click OK.
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.]
Save the record again.
The warning message appears again.
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.

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
Extension	viajesmundiales.net	viajesmundiales.net	viajemundiales.net	worldtravelinc.net
Address	Primero 3344	Segundo 1415	Circulo 440	11 Piney Ridge
City	Buenos Aires	Buenos Aires	Buenos Aires	San Diego
State				CA
Region
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
Extension	worldtravelinc.net	worldtravelinc.net	worldtravelinc.net	viajesmundiales.net
Address	6633 Harding Road	42 West Beach Drive	1442 West Columbia Dr.	Primero 5843
City	Nashville	Sydney	Franklin	Buenos Aires
State	TN	NSW	TN
Region
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
Notes
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.

Tables & Queries:
Validation

Validate a Field

Other Ways to Restrict Field Values

Validate a Record

Other Validation Rules

Process of Validating

Order of Validation:

Test Validation Rules

Step-by-Step: Field & Record Validation