Resources for Apr 26, 2010

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.