Date Not Now

Seems to be a trend.

Using >=Now() in a validation rule for a date. Trouble is it won't let you enter todays date if you do something like that.

So, don't use Now(), use Date(). Then it'll work.

Promise.

Oh, there's a Default Value option?

Yes, there's a default value option in the field properties bit of database table design.

So use it already!

There's all sorts of reasons and situation when you might use this feature. For starters, you might have a common value that many times will need to go into the box. Here's an example - let's say it's a stock control system being designed by someone who is completely hypothetically called "Steph"...

The field here is one where the user can say whether or not a bill has been paid.

The chances are that when the stock is booked in the bill won't have been paid - a bill is sent afterwards (the designer knows this because she spoke the the client - she knows the client's needs...). So a No is by far the most common value to go into the field when the record is created.

So, we use a default value of No!

You can also see some simple validation and whathaveyou. Of course, an alternative might have been to have used a Yes/No Data Type rather than a Text field...

Now, the same database also needs to enter the Date In of the stock being stored. Here again we can use a default value - the chances are that most of the time the value will be the current date.

Again, the designer knows this because she spoke to the client about it and found out. For sure. Can I stress that this step is really, really important in all of this? Thanks.

Here's the field properties:

We can use Date() to get the current date in that cell.

Note the validation here.

It's possible, although unlikely, that a different date might need to be entered. A container might be fortgotten about or not entered for some reason. Because of that the validation rule has been set to accept any date from 7 days before today until tomorrow - a container might sometimes be logged on the system the day before it's actually present on site.

Do I need to tell you how she knows this?

Here's the Planned Date Out field properties:

The minimum length of time a container can be in store for is 1 week apparantly. Seems a bit odd, but that's the way it works. So the Default Value can be set to a weeks time - Date()+7. The Valudation rule here might need to be >Date()+6 actually - I think you'd need to test all of this.

---

Now, testing...

All these rules and default values need testing. This goes in the Testing section of a report - in the Test Plan. In all this you clearly have to test the Boundary Values as well as values which are obviously going to the right and those that are obviously going to be wrong.

So, if I'm testing this all today (10/3/09) then I'd be checking if I could enter 2/1/06 (hopefully no - it's clearly wrong), 9/3/09 (OK), 2/3/09 (shouldn't accept it - and it's a boundary value) and 3/3/09 (should accept it - it's a boundary value again).

This demonstrates the importance of a clear test plan at this sort of level - it shows you the detail of testing that systems like this need.

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...