Weird Access Date Validation Problem

Conanius · 27 Apr 2006 at 11:22

All,

trying to sort an access database which I need to only accept date set from now to 3 years in the future. (it can not be over 3 years in advance)

What validation code should I be using for this?

ClearChaos · 27 Apr 2006 at 12:25

take a look at this

http://www.alvechurchdata.co.uk/accdate.htm

Fezzer · 27 Apr 2006 at 12:32

The simple answer is

Code:

Between Date() And DateAdd("yyyy",3,Date())

Although you might come across issues if you use this as a validation rule for a field in a table.

Conanius · 27 Apr 2006 at 21:46

Fezzer said:
The simple answer is

Code:

Between Date() And DateAdd("yyyy",3,Date())

Although you might come across issues if you use this as a validation rule for a field in a table.

ok,

We need it to be used in a table for people booking the use of something, and the dates obv can only be Today or up to 3 years in the future, so with this scenario, where are we going wrong?

Fezzer · 28 Apr 2006 at 10:39

Imagine that you create a new record and put todays date in the validated field, it meets the validation criteria and all is well with the world!! Tomorrow you go back to that record to edit a field in it, but you are now unable to save the record as the date entered no longer meet the validation.

This sort of record validation really needs to be done, in a form for example, at the point of entry of the information rather than at table level.

Unless, of course, you are never going to edit any of the records once they have been entered!!

I hope this helps

Conanius · 29 Apr 2006 at 21:26

Ive spoken to a few people, and the data wouldnt be edited after it had been entered (data will be entered through a form info the table I believe)

does that help?

Conanius · 3 May 2006 at 23:07

no one?

Fezzer · 4 May 2006 at 11:46

As the validation is dynamic, i.e. the values will change with time, I would recommend that you do the validation on the form level as the data is entered and not on the table.