Field Validation > or = Current Year

[neenmarie]

I'm trying to setup validation for a field in a table. The user will be
entering only four digits of a year; ie: 2006. I need to be sure they enter
only current or future years but don't know how to write that. Any help?
Thank you

 

['69 Camaro]

Hi.

Open the table in Design View, then select the field that stores the year.
In the field's Validation Rule Property, try:

=Year(Date())

In the field's Validation Text Property, try:

Please type only current or future years.

Save the table and close it. All updateable forms and queries with this
table as a Record Source will use this validation rule for this field.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[neenmarie]

Pefect...Thank you

Hi.

Open the table in Design View, then select the field that stores the year.
In the field's Validation Rule Property, try:


In the field's Validation Text Property, try:

Please type only current or future years.

Save the table and close it. All updateable forms and queries with this
table as a Record Source will use this validation rule for this field.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[peregenem]

Open the table in Design View, then select the field that stores the year.
In the field's Validation Rule Property, try:


In the field's Validation Text Property, try:

Please type only current or future years.

For increased perfection, the OP could give the CONSTRAINT a meaningful
name e.g.

CurrentProject.Connection.Execute _
"ALTER TABLE MyTable ADD" & _
" CONSTRAINT date_value_current_or_future" & _
" CHECK (date_value >= YEAR(DATE()))"

So when a user tries to change to an illegal value e.g.

CurrentProject.Connection.Execute _
"UPDATE MyTable SET data_value = 1969"

they will get a meaningful message i.e. would you prefer users to see

One or more values are prohibited by the validation rule
'Check_6609EA5E_71D8_48FF' set for 'MyTable'.

OR

One or more values are prohibited by the validation rule
'date_value_current_or_future' set for 'MyTable'.

 

[Rick Brandt]

For increased perfection, the OP could give the CONSTRAINT a
meaningful name e.g.

CurrentProject.Connection.Execute _
"ALTER TABLE MyTable ADD" & _
" CONSTRAINT date_value_current_or_future" & _
" CHECK (date_value >= YEAR(DATE()))"

So when a user tries to change to an illegal value e.g.

CurrentProject.Connection.Execute _
"UPDATE MyTable SET data_value = 1969"

they will get a meaningful message i.e. would you prefer users to see

One or more values are prohibited by the validation rule
'Check_6609EA5E_71D8_48FF' set for 'MyTable'.

OR

One or more values are prohibited by the validation rule
'date_value_current_or_future' set for 'MyTable'.

Um, they will see the validation rule text provided in the first response which
is even more "meaningful" than either of those two don't you think?

 

[peregenem]

they will see the validation rule text provided in the first response which
is even more "meaningful" than either of those two don't you think?

Yes