Software

Use Access table validation rules to protect data integrity

When the validity of data in one field depends on what's entered in another field, you'll want to build a validation rule. Here's a step-by-step look at the process.

Field validation rules ensure that any data entered into a field conforms to business rules. But what if the data entered into one field is dependent upon what is entered into another field? For example, say that a company has set maximum credit limits for customers from certain states; therefore, the rule for the Creditlimit field is determined by the State field. To set the table properties for this validation rule, follow these steps:

  1. Open the Customer table in Design View.
  2. Open the table property sheet (Figure A).

Figure A

  1. Click in the Validation Rule property box and then click the Build button.
  2. In the Expression Builder Dialog box, type the following expression at the prompt:

[State]In ("MO") AND [CreditLimit]<=30000 Or [State] In("IL") AND CreditLimit]<=20000 Or [State]Not In ("MO","IL")

  1. Click OK.
  2. Click in the Validation Text box of the Table's Property Sheet and enter Value entered is above customer's credit limit (Figure B).

Figure B

Now when an attempt is made to enter a credit limit amount above the maximum for the customer's location, an error message is displayed (Figure C).

Figure C


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks