Testing Validation Rule Changes

You learned in Chapter 27 how to define both field and table validation rules. You can also change these rules even after you have data in your table. Access 2007 warns you if some of the data in your table doesn’t satisfy the new rule, but it doesn’t tell you which rows have problems.

Checking a New Field Validation Rule

The best way to find out if any rows will fail a new field validation rule is to write a query to test your data before you make the change. The trick is you must specify criteria that are the converse of your proposed rule change to find the rows that don’t match. For example, if you are planning to set the Required property to Yes or specify a Validation Rule property of Is Not Null on a field (both tests mean the same thing), you want to look for rows containing a field that Is Null. If you want to limit the daily price of a room to <=90, then you must look for values that are >90 to find the rows that will fail. Another way to think about asking for the converse of a validation rule is to put the word Not in front of the rule. If the new rule is going to be <=90, then Not <=90 will find the bad rows.

Let’s see what we need to do to test a proposed validation rule change to tblFacilityRooms in the sample database. The daily room rate should not exceed $90.00, so the new rule in the DailyRate field will be <=90. To test for rooms that exceed this new limit, start a new query on tblFacilityRooms. Include the fields FacilityID, RoomNumber, RoomType, DailyRate, and WeeklyRate in the query’s design grid. (You need at least FacilityID and RoomNumber-the primary key fields-to be able to identify which rows fail.) Under DailyRate, enter the converse of the new rule: either >90 or Not <=90. Your query should look like Figure 28–29.

Figure 28–29: Create a new query to test a proposed new field validation rule.

If you run this query against the original data in the sample database, you’ll find 26 rooms that are priced higher than the new proposed rule. As you’ll learn in “Working in Query Datasheet View” on page 940, you can update these rows by typing a new value directly in the query datasheet.

Let’s try something. Select one of the invalid values you found in the query datasheet and try to type the new maximum value of $90.00. If you try to save the row, you’ll get an error message because there’s a table validation rule that prevents you from setting a DailyRate value that when multiplied by 7 is more than the WeeklyRate value. It looks like you’ll have to fix both values if you want to change your field validation rule.

Checking a New Table Validation Rule

Checking a proposed new field validation rule is simple. But what about making a change to a table validation rule? Typically, a table validation rule compares one field with another, so to check a new rule, you’ll need more complex criteria in your query.

There’s already a table validation rule in the tblFacilityRooms table in the  HousingDataCopy.accdb sample database. The rule makes sure that the weekly rate is not more than 7 times the daily rate-it wouldn’t be much of a discount if it were! Suppose you now want to be sure that the weekly rate reflects a true discount from the daily rate. Your proposed new rule might make sure that the weekly rate is no more than 6 times the daily rate-if an employee stays a full week, the last night is essentially free. Your new rule might look like the following:

 ([Dai1yRate]*6)>=[WeeklyRate]

So, you need to write a query that checks the current values in the WeeklyRate field to see if any will fail the new rule. Note that you could also create an expression to calculate DailyRate times 6 and compare that value with WeeklyRate. When the expression you want to test involves a calculation on one side of the comparison with a simple field value on the other side of the comparison, it’s easier to compare the simple field with the expression. Remember, you need to create the converse of the expression to find rows that won’t pass the new rule.

You can start with the query you built in the previous section or create a new query. You need at least the primary key fields from the table as well as the fields you need to perform the comparison. In this case, you need to compare the current value of WeeklyRate with the expression on DailyRate. Let’s turn the expression around so that it’s easier to see what you need to enter in the query grid. The expression looks like this:

 [WeeklyRate]<=([Dai1yRate]*6)

To test the converse on the WeeklyRate field’s Criteria row of your query, you need either

 >([DailyRate]*6)

or

 Not <=([DailyRate]*6)

Your test query should look like Figure 28–30.

Figure 28–30: You can create a query to test a new table validation rule.

If you run this query, you’ll find that nearly all the rows in the table fail the new test. When we loaded sample data into the table, we created weekly rates that are approximately 6.4 times the daily rate-so none of the rates pass the new test.