Cart    Contact Us    Tutorials    Downloads    About Us    News

MDE to MDB Conversion Service
(plus ACCDE to ACCDB Conversion)

- convert compiled Access files back to their original formats including VBA code

Database Repair Service

- a trusted service for repairing corrupt Microsoft Access files

vbWatchdog   [Code Library]

- global error handling library for VBA & VB6 projects

vbMAPI   [Code Library]

- Outlook/MAPI library for VBA, .NET and C# projects

Code Protector   [Software]

- strengthens protection against reverse engineering of MDE/ACCDE files


Validation Rules

        36 votes: ****      44,063 views      4 comments
by Allen Browne, 23 March 2007    (All versions of Access)

Microsoft Access Tips for Casual Users

By Allen Browne.  Created March 2007.  Updated January 2009.


Validation Rules

Validation rules prevent bad data being saved in your table. Basically, they look like criteria in a query.

You can create a rule for a field (lower pane of table design), or for the table (in the Properties box in table design.) Use the table's rule to compare fields.

There is one trap to avoid. In some versions of Access, you will not be able to leave the field blank once you add the validation rule, i.e. you must enter something that satisfies the rule. If you need to be able to leave the field blank, add OR Is Null to your rule. (Some versions accept Nulls anyway, but we recommend you make it explicit for clarity and consistency.)

This article explains how to use validation rules, and concludes with some thought provoking on when to use them.

Validation Rules for fields

When you select a field in table design, you see its Validation Rule property in the lower pane.

This rule is applied when you enter data into the field. You cannot tab to the next field until you enter something that satisfies the rule, or undo your entry.

Examples:

To do this ... Validation Rule for Fields Explanation
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.)

 

Validation Rules for tables

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.

Conclusion

Validation rules are very useful for keeping bad data out of your tables, but be careful not to overdo them. You don't want to block things that might be valid, though unexpected.


HomeIndex of tipsTop

<< Back to tutorials index




Please Note: This is a cached tutorial (reproduced with permission).
To view the tutorial at its original location click here.



Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

Have your say - comment on this article.

What did you think of 'Validation Rules'?


1.

0 says...

01 April 2008

 
Excellent Help, Thanks :D

(5 Star)

2.

jhodi says...

28 August 2008

 
Wow dats an amazing site...It has help me a lot wen i had to start the project at scratch...2 excellent..Must say thx....

3.

Tony says...

14 April 2009

 
Another schooner I owe you Allen, certainly beats using access "help". Great stuff and thank you once again!

4.

Naufal says...

02 February 2010

 
I got what i wanted from this site super information!!!!!!!!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code
Please note: It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.   All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.


Site tools