Field Validation Rules for Blocking Bad Data - Writing a Field Validation Rule

(Page 3 of 4 )

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 certain 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.

Next: Validating text >>
 

More MS SQL Server Articles
More By O'Reilly Media

Comments On: Field Validation Rules for Blocking Bad Data

· This article is an excerpt from the book "Access 2007: The Missing Manual,"...   >>> Post your comment now!

Buy this book...

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.