Validation Rules for tables

Data Validation

Data Validation is a very important database concept. It is the process by which Access tests the data that is being entered into the database, to make sure it is in an acceptable, or valid, format.

Imagine that one of your database users has entered an order date of January 4, 2008 in the month/date/year format, as 01/04/2008. Another user has entered an order placed on that same date in the day/month/year format, as 04/01/2008. Now, if the database is tracking all sales for the month of January 2008, it may not show both orders as placed in January, even though both were placed on the same date.

Access 2007 allows you to set field properties and data validation rules, to force the person entering data to follow a specific format.

Data Types and Validation Rules

Data Validation begins when data types are set during the process of building tables and fields. For example, if a field data type had been set to Currency and a text value is entered in that table field during data entry, Access will not accept an invalid format and will display a validation error, like the one below.

Data Validation Error Message

Data validation is accomplished by setting data validation rules and other field properties for various fields. 

Setting Data Validation Rules

• In Design View, highlight the field that requires a validation rule.
• In the Field Properties section at the bottom half of the window, set your validation rule using the Expression Builder. The expression builder offers common syntax to set up a data validation rule.
Use Expression Builder to Enter Validation Rule
Validation Rule for Category Field

Validation rules work most easily with numeric fields. Rules for text fields require you to enclose each acceptable value inside its own quotation marks, separating them with Or, as seen above.

Validation Text

Validation Text is a specialized error message that you can set to have Access tell the person entering data the specific way you would like them to enter it.

To set the Validation Text, enter the error message exactly as you want it to appear to your user in the row directly beneath the Validation Rule row in the Field Properties section of Design View. For the validation rule that we set for Category, you would set the validation text like this:

Validation Text for Category Field

The following image shows the resulting error message that the user would see when they have broken the Category validation rule:

Error Message Showing Validation Text

Using Field Properties to Ensure Data Integrity

Another way to ensure data integrity is by setting field properties such as Field Size, Format, and Required.

• Field Size can be set to hold a specific number of characters, up to as many as 255 for text fields. If you were using a text field to hold the two letter state postal abbreviation, the field size could be set to 2 to ensure that no one enters a full state name into this field.
• The Format field property can be set to display text or numbers in a standardized way. For example, text can be set to show as all uppercase and numbers can be set to show scientific numbers, percentages, or decimals.
• Set the Required property to Yes if you want the person entering data to be required to enter something in the field. Choose No if the person entering data is allowed to leave the field blank.

These are just some ways Access helps you ensure that data being entered into your database is valid.

To do this ... Validation Rule for Fields Explanation


all bold statement are command


Accept letters (a - z)
only Is Null OR Not Like "*[!a-z]*" 
Any character outside the range A to Z is rejected. (Case insensitive.)

Accept digits (0 - 9)
only Is Null OR Not Like "*[!0-9]*"
Any character outside the range 0 to 9 is rejected. (Decimal point and negative sign rejected.)

Letters and spaces only
Is Null Or Not Like "*[!a-z OR "" ""]*" 
Punctuation and digits rejected.

Digits and letters only
Is Null OR Not Like "*[!((a-z) or (0-9))]*"
 Accepts A to Z and 0 to 9, but no punctuation or other characters.

Exactly 8 characters
Is Null OR Like "????????" 
The question mark stands for one character.

Exactly 4 digits
 Is Null OR Between 1000 And 9999
 For Number fields.


Is Null OR Like
 "####" 
For Text fields.

Positive numbers only
Is Null OR >= 0 
Remove the "=" if zero is not allowed either.


No more than 100%
Is Null OR Between -1 And 1 100% is 1. 
Use 0 instead of -1 if negative percentages are not allowed.

Not a future date
Is Null OR <= Date()


Email address
Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*"))
 Requires at least one character, @, at least one character, dot, at least one character. Space, comma, and semicolon are not permitted.

You must fill in Field1
Not Null
Same as setting the field's Required property, but lets you create a custom message (in the Validation Text property.)

Limit to specific choices
Is Null OR "M" Or "F" 
It is better to use a lookup table for the list, but this may be useful for simple choices such as Male/Female.
Is Null OR IN (1, 2, 4, 8)
The IN operator may be simpler than several ORs.
Yes/No/Null field Is Null OR 0 or -1

The Yes/No field in Access does not support Null as other databases do. To simulate a real Yes/No/Null data type, use a Number field (size Integer) with this rule. (Access uses 0 for False, and -1 for True.)





In table design, open the Properties box and you see another Validation Rule. This is the rule for the table.
The rule is applied after all fields have been entered, just before the record is saved. Use this rule to compare values across different fields, or to delay validation until the last moment before the record is saved.
Examples:
To do this ... Validation Rule for Table Explanation
A booking cannot
end before it starts ([StartDate] Is Null) OR
([EndDate] Is Null) OR
([StartDate] <= [EndDate])

 The rule is satisfied if either field is left blank; otherwise StartDate must be before (or the same as) EndDate.
If you fill in Field1,

Field2 is required also ([Field1] Is Null) OR ([Field2] Is Not Null)
The rule is satisfied if Field1 is blank; otherwise it is satisfied only if Field2 is filled in.
You must enter Field1

or Field2, but not both ([Field1] Is Null) XOR ([Field2] Is Null) XOR is the exclusive OR.

When to use validation rules
In designing a database, you walk a tightrope between blocking bad data and accepting anything. Ultimately, a database is only as good as the data it contains, so you want to do everything you can to limit bad data. On the other hand, truth is stranger than fiction, and your database must handle those weird real-world cases where the data exceeds the bounds of your imagination.
Field's validation rule
Take a BirthDate field, for example. Should you create a rule to ensure the user doesn't enter a future date? We would need some radically different physics to ever be entering people who are not yet born, so it sounds like a safe enough rule? But did you consider that the computer's date might be wrong? Would it be better to give a warning rather than block the entry?
The answer to that question is subjective. The question merely illustrates the need to think outside the box whenever you will block data, not merely to block things just because you cannot imagine a valid scenario for that data.
Validation Rules are absolute. You cannot bypass them, so you cannot use them for warnings. To give a warning instead, use an event of your form, such as Form_BeforeUpdate.
Table's validation rule
We suggested using this rule for comparing fields. In the ideal database design, the fields are not dependent on each other, so if you are comparing fields, you might consider whether there is another way to design the table.
Our first example above ensures that a booking does not end before it starts. There is therefore a dependency between these two fields. Could we redesign the table without that dependency? How about replacing EndDate with a Duration field? Duration would be a number in an applicable unit (e.g. days for hotel bookings, periods for school classrooms, or minutes for doctors appointments.) We use a calculated field in a query to get the EndDate. This may not be the best design for every case, but it is worth considering when you go to use the record-level validation rule.
Limitations
You cannot use a validation rule where:
• You want to call user-defined functions, or VBA functions beyond the ones in JET such as IIf() and Date().
• The user should be able to bypass the rule.
• The expression is too complex.
• The expression involves data in other records or other tables. (Well, not easily, anyway.)
Alternatives
Use these alternatives instead of or in combination with validation rules:
• Required: Setting a field's Required property to Yes forces the user to enter something. (In addition to the obvious cases, always consider setting this to Yes for your foreign key fields. See #3 in this article for details.)
• Allow Zero Length: Setting this property to No for text, memo, and hyperlink fields prevents a zero-length string being entered. A ZLS is not the same as a Null, so if you permit this you have confusing data for the user, more work checking for both as a developer, more chance of a mistake, and slower executing queries. More information in Problem Properties.
• Indexed: To prevent duplicates in a field, set this property to Yes (No Duplicates). Using the Indexes box in table design, you can create a multi-field unique index to the values are unique across a combination of fields.
• Lookups: Rather than creating a validation rule consisting of a list of valid values, consider creating a related table. This is much more flexible and easier to maintain.
• Input Mask: Of limited use. Users must enter the entire pattern (without them you can enter some dates with just 3 keystrokes, e.g. 2/5), and they cannot easily insert a character if they missed one.

Challenge!

If you haven't already done so, save the sample Ready2Read database on your own computer.

• Open the database, and add records using the new record navigation button
• Add a record using the New Record command in the Ribbon
• Edit a record using the Find and Replace command.
• Copy and Paste a record in one of the tables
• Set a validation rule and validation text for a field in one of your tables. Then, break the rule when entering data and see the resulting message.