Validation Rules

[Elana]

This is my first time working with Microsoft access; I am using the 2007
version. I need to make a validation rule that only allows me to input the
number 1-7. If I know how to do that I think I will easily be able to make
other rules based off of that. I also have a field for the year and I want it
so that you can only put in the year 2009. How do you do that? I have been
working on this for that past 3 days and am going a little crazy. If anyone
can help with a clear explanation I would greatly appreciate it.

E

 

[Jeanette Cunningham]

Hi Elana,
this site has lots of examples of table validation rules.

http://allenbrowne.com/ValidationRule.html

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Elana]

I have been to that website before. I did not find that website any more
helpful than the help option in access; it had very similar information. I
will give you an example. To be able to input only numbers 1-7, I used: Is
Null Or In (>8). When I tried to input numbers into the form, I kept getting
an error saying to put in a valid number.

 

[troy23]

In the validation rule of the text box you could add

In (1,2,3,4,5,6,7)

and in the validation text add something like "Wrong - pls retry"
without the quotation marks

When you enter a number and then tab away from the control the message
will be fired if the wrong value is entered.

For FREE Access ebook and videos click here
http://access-databases.com/ebook

 

[Elana]

I have the data type set to number right now. Should it be set to text? and
if so, can you please explain why? Thank you.

 

[Jeff Boyce]

You've described what you want to do, but not why. If you'll explain a bit
more what having only "2009" or only 1-7 will allow you to do, folks here
may be able to offer alternate approaches.

For example, if you are using a text or number field in your table to hold
the year 2009, you might be able to use an Access Date/Time field to hold an
entire date (and time) in a single field.

More specific description may lead to more specific suggestions...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have been to that website before. I did not find that website any more
helpful than the help option in access; it had very similar information. I
will give you an example. To be able to input only numbers 1-7, I used: Is
Null Or In (>8). When I tried to input numbers into the form, I kept getting
an error saying to put in a valid number.

Try

IS NULL OR (>0 AND <8)

For the 2009... do you want that restriction as a permanent part of the
database? Or do you want it to be 2010 starting next January? If that's the
only value allowed, why store it at ALL? If it's the current year, you could
simply set the Default Value of the field to

=Year(Date())

to get the current year.

If you really do want it as a validation rule, then it's simply

=2009

but as I say, that seems rather pointless!