How to Limit Data in a Microsoft Access Field to Certain Values

Limiting data in a field prevents entry errors. For instance, suppose you are selling towels in three colors - red, blue, and yellow. Limiting the data values to "red," "blue" or "yellow" will prevent people from mistakenly ordering pink towels. These instructions apply to MS Access 97.

Difficulty: Moderate

Instructions

Things You'll Need:

• Microsoft Access

1. Open your table in Design view.

2. Click in the field that is to be limited.

3. In the Field Properties box at the bottom of the screen, click in Validation Rule. A small box with three dots appears. Click in this box.

4. The Expression Builder appears. Enter the expressions to limit your data. In the towel example given above, you would click on Functions, then enter Like "red" OR "blue" OR "yellow."

5. Click OK. Your field will no longer accept any values other than red, blue, or yellow.

6. Directly under the Validation Rule field, you will see a field called Validation Text. This is what users will see if they try to enter the wrong value by mistake. Enter your validation text here. In the towel example, you might use something like, "That color is not available. Please enter red, yellow or blue."

7. Save your table.

Tips & Warnings

• The Expression Builder is complex. If you are inexperienced, expect to spend some time learning how to create these expressions. Refer to Access help, or just try an expression you think might work, then try entering data in your table and see what happens. Go back and change your expression (following the same steps above) if necessary.