Field Validation Rules for Blocking Bad Data - Validating text
(Page 4 of 4 )
With text, validation lets you verify that a value starts with, ends with, or contains specific characters. You perform all these tasks with the Like operator, which compares text to a pattern.
This condition forces a field to start with the letter R:
Like "R*"
The asterisk (*) represents zero or more characters. Thus, the complete expression asks Access to check that the value starts with R (or r), followed by a series of zero or more characters.
You can use a similar expression to make sure a piece of text ends with specific characters:
Like "*ed"
This expression allows the values talked, walked, and 34z%($)#ed, but not talking, walkable, or 34z%($)#.
For a slightly less common trick, you can use more than one asterisk. The following expression requires that the letter a and b appear (in that order but not necessarily next to each other) somewhere in a text field:
Like "*a*b*"
Along with the asterisk, the Like operator also supports a few more characters. You can use ? to match a single character, which is handy if you know how long text should be or where a certain letter should appear. Here’s the validation rule for an eight-character product code that ends in 0ZB:
Like "?????0ZB"
The # character plays a similar role, but it represents a number. Thus, the following validation rule defines a product code that ends in 0ZB and is preceded by five numbers:
Like "#####0ZB"
And finally, you can restrict any character to certain letters or symbols. The trick’s to put the allowed characters inside square brackets.
Suppose your company uses an eight-character product code that always begins with A or E. Here’s the validation rule you need:
Like "[AE]???????"
Note that the [AE] part represents one character, which can be either A or E. If you wanted to allow A, B, C, D, you’d write [ABCD] instead, or you’d use the handy shortcut [A-D], which means “allow any character from A to D, including A and D.”
Here’s one more validation expression, which allows a seven-letter word, and doesn’t allow numbers or symbols. It works by repeating the [A-Z] code (which allows any letter) seven times.
Like [A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]
As you can see, text validation expressions aren’t always pretty. Not only can they grow to ridiculous sizes, but there are lots of restrictions they can’t apply. You can’t, for instance, let the length of the text vary between a minimum and maximum that you set. And you can’t distinguish between capitalized and lowercase letters.
Note: You can get around many of these limitations using some of the functions that Access provides. On page 228, you’ll learn how to use functions that can snip out bits of text, test lengths, check
capitalization, and more.
Combining validation conditions
No matter what the data type, you can also combine your conditions in two different ways. Using the And keyword, you can create a validation rule that enforces two requirements. This trick’s handy, because each field can have at most a single validation rule.
To use the And keyword, just write two validation rules and put the word And in between. It doesn’t matter which validation rule’s first. Here’s a validation rule that forces a date to be before today but later than January 1, 2000:
<Date() And >#1/1/2000#
You can also use the Or keyword to accept a value if it meets either one of two conditions. Here’s a validation rule that allows numbers greater than 1000 or less than –1000:
>1000 Or <-1000
Please check back tomorrow for the conclusion to this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter four of the book Access 2007: The Missing Manual, written by Matthew MacDonald (O'Reilly, 2006; ISBN: 0596527608). Check it out today at your favorite bookstore. Buy this book now.
|
|
