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.

Easy:

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.

Image

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.