More time validation

I mentioned table level validation rules already. They’re handy for things like making it so that the date a job is finished has to be after the date a job has started.

You can also use them if you want to combine two sets of validation rules for a specific field.

So, say that you want to only allow a time between a set of times. That’s easy – you simply use a time validation rule at the field level. But if you also want to make it so that times can only end in :00 or :30 (so an appointment can only be made at half past or on the hour) then it’s trickier.

Here you need to combine table level and field validation.

1. Start by dealing with the field level validation for the set of times. Check it and make sure it works!

2. Now click the Property Sheet button (see Table level validation rules)

3. Now add a validation rule to make the minutes only able to end in :00 or :30:

  • Obviously you could add more Or conditions into this to allow ending with 15 or 45. Or even more to allow ending in 10, 20 etc…
  • My field name in this is jobTime. You’d need to change this to your field name

4. Don’t forget the validation text – make it helpful

5. Test and check that it works. Don’t forget that table level validation will only check the rule once you move off the row you’re entering data in.

6. Test the combinations – can you enter a time not in the range that ends in :00? What about a time not in the range that ends in :57? Or a time in the range that ends in :57?

There you go – table level validation magic!

Multiple Table Level Validation:

Now, you can also add more than one validation rule at table level if you’re clever. So, for example, if I want to combine my rule for making the date the job is completed after the date the job was started and my new rule for validating the minutes then I can do:

The trick with this is probably writing a sensible validation text message!


Validating times in Access

Oh, I forget so much these days.

I’m sure I knew how to do this, but the trouble with databases is that I only do this level of work once a year so I’m forever forgetting how to do stuff.

So, validating a time field in Access so that values can only be between certain times. Like the times a shop is open for.


Between #09:00:00# and #17:00:00#

will do the job (in this case I want a time between 9am and 5pm). The #’s are the key thing that I forgot.


I imagine this will work for Medium and Long Time fields as well. Probably anyway. You probably shouldn’t be using General Date field types so that’s not a problem.

Table level validation rules

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!