You cannot bypass them, so you cannot use them for warnings. 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. The answer to that question is subjective. 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? 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. Ultimately, a database is only as good as the data it contains, so you want to do everything you can to limit bad data. In designing a database, you walk a tightrope between blocking bad data and accepting anything. The rule is satisfied if Field1 is blank otherwise it is satisfied only if Field2 is filled in. The rule is satisfied if either field is left blank otherwise StartDate must be before (or the same as) EndDate. Use this rule to compare values across different fields, or to delay validation until the last moment before the record is saved. The rule is applied after all fields have been entered, just before the record is saved. In table design, open the Properties box and you see another Validation Rule. (Access uses 0 for False, and -1 for True.) To simulate a real Yes/No/Null data type, use a Number field (size Integer) with this rule. The Yes/No field in Access does not support Null as other databases do. The IN operator may be simpler than several ORs. It is better to use a lookup table for the list, but this may be useful for simple choices such as Male/Female. Same as setting the field's Required property, but lets you create a custom message (in the Validation Text property.) Space, comma, and semicolon are not permitted. Requires at least one character, at least one character, dot, at least one character. Use 0 instead of -1 if negative percentages are not allowed. Remove the "=" if zero is not allowed either.ġ00% is 1. The question mark stands for one character. Is Null OR Not Like "**"Īccepts A to Z and 0 to 9, but no punctuation or other characters. (Decimal point and negative sign rejected.) (Case insensitive.)Īny character outside the range 0 to 9 is rejected. You cannot tab to the next field until you enter something that satisfies the rule, or undo your entry.Īny character outside the range A to Z is rejected. This rule is applied when you enter data into the field. When you select a field in table design, you see its Validation Rule property in the lower pane.
This article explains how to use validation rules, and concludes with some thought provoking on when to use them. (Some versions accept Nulls anyway, but we recommend you make it explicit for clarity and consistency.) If you need to be able to leave the field blank, add OR Is Null to your rule. you must enter something that satisfies the rule. In some versions of Access, you will not be able to leave the field blank once you add the validation rule, i.e. 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. Basically, they look like criteria in a query. Validation rules prevent bad data being saved in your table. Microsoft Access tips: Validation Rules Microsoft Access Tips for Casual Usersīy Allen Browne.