Our first example above ensures that a booking does not end before it starts.There is therefore a dependency between these two fields.
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. The rule is applied after all fields have been entered, just before the record is saved.
When data is entered, Access checks to see whether the input breaks a validation rule – if so, the input is not accepted, and Access displays a message.
Access provides a number of ways to restrict input: You can use these methods of validating data alone or in combination with each other.
Validation rules prevent bad data being saved in your table. 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. In some versions of Access, you will not be able to leave the field blank once you add the validation rule, i.e. If you need to be able to leave the field blank, add OR Is Null to your rule.
This article explains how to use validation rules, and concludes with some thought provoking on when to use them.
Would it be better to give a warning rather than block the entry? 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. 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_Before Update. 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.
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 End Date.
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.