Validation rules in Access are relatively straightforward.
Setting a range of numbers is fine, making a number positive is fine, choosing from a set of values is fine. But it gets more complex if you need to add multiple validation rules for particular fields or if you need to compare one field to another.
Say, for example, you want to make it so that the date a job is finished has to be after the date a job has started.
Standard validation won’t work in this case – you need table level validation rules instead. Fortunately these are pretty easy to deal with. More or less.
1. Open the table you need to validate within in Design View
2. Hit the Property Sheet button to pop the property sheet up. Make it as wide as you need to.
3. Write the validation rule in the way you’d usually do, using the same kind of ideas you might if you were dealing with fields in a query.
So, to deal with the date example, try this:
- My field names are in this. You’d, obviously, change the field names to match your own.
4. Make the validation text helpful and useful
5. Try it out.
Note: the validation won’t immediately pick up and error when you enter the data. You need to click off the row you’re entering data in in the table (or click Save on the form etc…) for it to pick up the error and pop up the error message. That doesn’t matter – the key is that it’s checking that the data is valid and then giving a helpful error message if it’s not!