Field Validation Rules for Blocking Bad Data

Yesterday we discussed using masks to help with data validation issues. This time we’ll be finishing our discussion of masks and talk about applying field validation rules. This article is excerpted from chapter four of the book Access 2007: The Missing Manual, written by Matthew MacDonald (O’Reilly, 2006; ISBN: 0596527608). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Validation Rules

POWER USERS’ CLINIC

Adding Your Mask to the Mask List

Sometimes you may create a mask that’s so useful you want to use it in many different tables in your database (and maybe even in different databases). While you can certainly copy your mask to every field that needs to use it,
Access has a nicer option—you can store your mask in its mask list. That way, the mask shows up whenever you run the Input Mask wizard, right alongside all Access’s other standard masks.

To add your mask to the list, head to the Input Mask field property (for any field), and then click the ellipsis button to fire up the Input Mask wizard. Then, click the Edit List button, which pops up a handy window where you can edit the masks that Access provides, and add your own (Figure 4-13).


Figure 4-13.
To add your own mask, use the record scrolling buttons (at the bottom of this window) to scroll to the end. Or you can use this window to change a mask. For example, the prebuilt telephone mask doesn’t require an area code. If that’s a liberty you’re not willing to take, then replace it with the more restrictive version (000) 000-0000.

Input masks are a great tool, but they apply to only a few specific types of information—usually fixed-length text that has a single, unchanging pattern. To create a truly bulletproof table, you need to use more sophisticated restrictions, like making sure a number falls in a certain range, checking that a date hasn’t yet occurred, or verifying that a text value starts with a certain letter. Validation rules can help you create all these restrictions by drawing on the full power of the SQL language.


Note: You’ll get a more thorough introduction to SQL starting in Chapter 6. Fortunately, you need only a dash of SQL to write a validation rule. The key ingredient’s a validation expression, and you’ll see several practical examples of expressions that you can drop straight into your tables.


A validation rule’s premise is simple. You set up a restriction that tells Access which values to allow in a field and which ones are no good. Whenever someone adds a new record or edits a record, Access makes sure the data lives up to your validation rules. If it doesn’t, then Access presents an error message and forces you to edit the offending data and try again.

{mospagebreak title=Applying a Field Validation Rule}

Each field can have a single validation rule. The following set of steps show you how to set one up. You’ll start out easy, with a validation rule that prevents a numeric field from accepting 0 or any negative number (and in the following sections you’ll hone your rule-writing abilities so you can tackle other data types).

Here’s how to add your validation rule:

  1. In Design view, select the field to which you want to apply the rule.

    All data types—except Memo, AutoNumber, and OLE Object—support validation. The validation rule in this example works with any numeric data type (like Number or Currency). 
  2. In the Validation Rule field property, type a validation expression (Figure 4-14).

    An expression’s a bit of SQL that performs a check on the data you’ve entered. Access performs its validation check when you finish entering a piece of data, and try to navigate to another field or another record. For example, >0 is a validation rule that forces the value in a Number field to be larger than 0. You’ll learn more validation rules in the following sections.


    Figure 4-14.
    Here, the Validation Rule property prevents
    impossible prices, and the Validation Text
    provides an error message
    .

  3. Type some error-message text in the Validation Text field property.

    If you enter a value that fails the validation check, then Access rejects the value and displays this error text in a dialog box. If you don’t supply any text, then Access shows the validation rule for the field (whatever you entered in step 2), which is more than a little confusing for most mere mortals. 


  4. Right-click the tab title, and then choose Datasheet View.

    If your table has existing records, Access gives you the option of checking them to make sure they meet the requirements of your validation rule. You decide whether you want to perform this check, or skip it altogether.

    Once you’re in Datasheet view, you’re ready to try out your validation rule (Figure 4-15).


    Figure 4-15.
    Here, a validation rule of >0 prevents negative
    numbers in the Price field. When you enter a
    negative number, Access pops up a message box
    with the validation text you defined, as shown
    here. Once you click OK, you return to your field,
    which remains in edit mode. You can change
    the value to a positive number, or press Esc to
    cancel the record edit or insertion.

Note: Just because your table has validation rules doesn’t mean the data inside follows these rules. A discrepancy can occur if you added records before the validation rules came into effect. (You learned about the same potential problem with required fields on page 116.) To avoid these headaches, set up your validation rules before you start adding data.


{mospagebreak title=Writing a Field Validation Rule}

As you can see, it’s easy enough to apply a validation rule to a field. But creating the right validation rule takes more thought. In order to get the result you want, you need to take your first step into the sometimes quirky world of SQL.

Although validation’s limited only by your imagination, Access pros turn to a few basic patterns again and again. The following sections give you some quick and easy starting points for validating different data types.


Note: Access uses your validation rule only if a field contains some content. If you leave it blank, then Access accepts if without any checks. If this isn’t the behavior you want, then just set the Required property to Yes to make the field mandatory, as described on page 116.


Validating numbers

For numbers, the most common technique’s to check that the value falls in a cer tain range. In other words, you want to check that a number’s less than or greater than another value. Your tools are the comparison signs < and >. Table 4-3 shows some common examples.

Table 4-3.  Expressions for Numbers

Comparison

Sample Expression

Description

Less than

<100

The value must be less than 100.

Greater than

>0

The value must be greater than 0.

Not equal to

<>42

The value can be anything except 42.

Less than or equal to

<=100

The value must be less than or equal to 100.

Greater than or equal to

>=0

The value must be greater than or equal to 0.

Equal to

=42

The value must be 42. (Not much point in asking anyone to type it in, is there?)

Between

Between 0 and 100

The value must be 0, 100, or somewhere in between.

Validating dates

As with numbers, date validation usually involves checking to see if the value falls within a specified range. Here, your challenge is making sure that your date’s in the right format for an expression. If you use the validation rule >Jan 30, 2007, Access is utterly confused, because it doesn’t realize that the text (Jan 30, 2007) is supposed to represent a date. Similarly, if you try >1/30/07, then Access assumes the numbers on the right are part of a division calculation.

To solve this problem, use Access universal date syntax, which looks like this:

  #1/30/2007#

A universal date always has the date components in the order month/day/year, and it’s always bracketed by the # symbol on either side. Using this syntax, you can craft a condition like >#1/30/2007#, which states that a given date must be larger than (fall after) the date January 30, 2007. January 31, 2007 fits the bill, but a date in 2006 is out.

The universal date syntax can also include a time component, like this:

  #1/30/2007 5:30PM#


Note: When comparing two dates, Access takes the time information into consideration. The date #1/30/2007# doesn’t include any time information, so it’s treated as though it occurs on the very first second of the day. As a result, Access considers the date value #1/30/2007 8:00 AM# larger, because it occurs eight hours later. 


Once you’ve learned the universal date syntax, you can use any of the comparison operators you used with numbers. You can also use these handy functions to get information about the current date and time:

  1. Date( ) gets the current date (without any time information, so it counts as the first second of the day).
  2. Now( ) gets the current instant in time, including the date and time information.

Note: A function’s a built-in code routine that performs some task, like fetching the current date from the computer clock. You’ll learn about many more date functions, which let you perform advanced tasks like finding the day of the week for a date, on page 229.


Table 4-4 has some examples.

Table 4-4.  Expressions for Dates

Comparison

Sample Expression

Description

Less than

<#1/30/2007#

The date occurs before January 30, 2007.

Greater than

>#1/30/2007 5:30 PM#

The date occurs after January 30, 2007, or on January 30, 2007, after 5:30 p.m.

Less than or equal to

<=#1/30/2007#

The date occurs before January 30, 2007, or on the first second of January 30, 2007.

Greater than or equal to

>=#1/30/2007#

The date occurs on or after Janu-ary 30, 2007.

Greater than the current date

>Date( )

The date occurs today or after.

Less than the current date

<Date( )

The date occurs yesterday or before.

Greater than the current date (and time)

>Now( )

The date occurs today after the current time, or any day in the future. 

Less than the current date (and time) 

<Now( )

The date occurs today before the current time, or any day in the past. 

{mospagebreak title=Validating text}

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 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 differ ent 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.

One thought on “Field Validation Rules for Blocking Bad Data

  1. This article is an excerpt from the book “Access 2007: The Missing Manual,” published by O’Reilly. We hope you found it to be enjoyable and educational. Please let us know what you thought of it, and if you would like to see more content of this nature.

[gp-comments width="770" linklove="off" ]