Date Validation for Databases

So, you need to validate a date for your database.

OK, so there are some easy things to do:

• you can set the field type to Date/Time


• you then need to set the Format in the Field Properties to Short Date probably - depending on the format you need

What else can you do? What can get you more marks and make you look like a champion database dude?

An input mask is possible - use 00/00/0000;;_ for example.

You can then set a validation rule of something like Date().

But you might want to get funkier than that. Look at the screenshot here.

This shows that we're validating the date to be between today-43800 days and today-3000 days. That's days OK - so I got the calculator out and figured out that 120 years old was 365 * 120 - easy as that.

This allows you to set a range of valid dates for, say, a date of birth if you need someone to be at least a certain age. The -43800 days bit means someone can't enter 1645 by mistake!

I've also set the Required property to Yes. This means that they user has to enter a valid date of birth. They aren't allowed to leave it blank. Take a bit of care with required properties though: if there's any chance that the client might not need to enter that field for any item then don't set required to yes!

Delivery Dates:

You might also need to set a delivery date (or a return date for a book or something similar).

Take a look at this for some ideas:

This time I'm setting a validation rule of between Date()+1 (i.e. tomorrow) and Date()+35, which is 35 days time. That's the set window I've got for delivering the product someone's ordered. The delivery date field has to be set within that window.

I've also set a default value here. My standard delivery date is tomorrow because the company does a next day delivery service. It will save time and effort having to type in the date each time if most of the deliveries will be made the next day.

For a library database I might set the default value for the return date to be Date()+14 for example - depending what the standard loan period is.

You can overwrite a default value, but it saves some time entering things that are going to be standard for many records if you use a default value.

Of course, you could get really clever and set up a calendar control on a form to make entering dates even easier - just like you see on the web for hotel booking or buying train tickets for example...