MS Access Validation Rules

[5614]

I have been given a task to come up with 4 different types of validation which I can implement into a database made in MS Access 2000. I know there are multiple ways of entering a validation rule (ie. Field Size or Input Mask) but for this task I need to do it in a specific way using the Validation Rule box, as shown in red below:

 

 

I have only been able to think/make 3 such validation rules:

 

#) Type of check --- Validation Rule

 

1) Presence check --- Is Not Null

2) Range check --- Between 1 And 10

3) Length check --- Like "?????"

 

I can think of a few 4th different type of check, a picture/format check for example, the kind of thing you would normally put into Input Mask, however I cannot get this to work using the Validation Rule. I've tried using "LL00" and "LLNN" to represent two letters followed by two numbers, but it doesn't work.

 

If anyone knows a different type of validation check and how to make it work with the Validation Rule then help would much be appreciated. Thanks in advance.

[RyanJ]

How about is a valid date range, something like this:

 

>= #1/1/05# And <#1/1/07#

 

Other then that I'm not shure what you can do - thesethings are limited by the inital field type anyway...

 

Cheers,

 

Ryan Jones

[5614]

Nah, I made a genius validation rule like this:

<=DateAdd("yyyy",-18,Date())

It basically ensures that the date (this was a date of birth field) was older than 18 years... ie. the input DOB was older than 18.

 

Anyway, then I realised that was afterall just a type of range check, so no good.

[RyanJ]

I suppose you can do things like "is valid xxx" check, for example is valid E-mail address or things of that nature.

 

Would that work for you?

 

Cheers,

 

Ryan Jones

[5614]

I don't follow.

 

The "Is" function can only be followed by "Null" or "Not Null" in MS Access 2000 (when you type an incorrect validation rule beginning with "Is" a popup tells you this).

 

So what exactly do you mean and how do you write it?

[RyanJ]

I don't follow.

 

The "Is" function can only be followed by "Null" or "Not Null" in MS Access 2000 (when you type an incorrect validation rule beginning with "Is" a popup tells you this)

 

I don't mean use the "Is" keyword, I mean do it yourself

 

http://dbforums.com/t745650.html

 

Is a possible example

 

Cheers,

 

Ryan Jones

[5614]

Looking at that gives me some ideas... also I've just thought of something else which should work, I hope! I will have to try these out tomorrow and will post back here when I've tried them.

 

Thanks RyanJ.

[5614]

Hmm, it's a shame that I don't have an email address in the database. I used a date format check by typing:

Like "*/*/*"

Where * is a wildcard and the / has to be typed. I guess that counts as a picture/format check.

 

Thanks a lot