MS Access

Validating Data

Without a doubt, is the most powerful tool you can use to prevent data-entry errors. With , Access actually tests data to make sure that it conforms to what you want to appear in the table. If the incoming data doesn't meet your requirements, Access rejects it and displays an error message.

Data validation tests incoming data to make sure it conforms to a specified set of rules.
Access displays the error message you entered whenever the validation rule is broken.

Data validation works best in number, currency, and date/time fields. You can create a validation rule for text entries, but doing so can be complicated especially if you want to test a lot of text variables.

There are actually two boxes that relate to data validation. They are the:

  • Validation Rule box: Used to specify the requirements for data entered into the field.

  • Validation Text box: Used to specify the message that will be displayed to the user when data that violates the validation rule is entered.

Creating data validation rules can be a little tricky you create a data validation using the same hard-to-remember operators that you use in filters and queries. Table contains some data validations that you can modify and use in your tables.

  1. If necessary, open the Lesson 4 database and double-click the tblCustomers table.

    The tblCustomers table opens.

  2. Switch to Design view by clicking the View button on the toolbar.

    You decide to specify that the DOB field cannot be later than today's date. (We can't have any people with birthdays in the future, can we?)

  3. Click the DOB field.

    Before we get started, you need to make sure that the DOB field is a Date/Time fieldyou should have changed this in a previous lesson.

  4. Verify that the DOB field's Data Type is set to Date/Time.

    First you need to enter a validation rule.

  5. Click the Validation Rule box and type Date( ).

    The validation rule you just entered will prevent users from entering dates later than today's date in the DOB field. Next you have to specify the error message that Access will display if someone tries to break your validation rule by entering a future date.

  6. Click the Validation Text box and type Date must not be later than today's date.

    Your table should look similar to figure. Let's test our new data validation rule.

  7. Click the Save button on the toolbar to save your changes, click No to close the message about testing existing data, and then click the View button to display the table in Datasheet view.

    Data validation rules apply to both new and existing records. Move on to the next step and try modifying a record so that it breaks our data validation rule.

  8. Click the DOB field for any record. Change the date to a future date that falls after today, and then press Enter.

    When you try to violate the validation rule, Access displays the Validation Text you entered back in Step 5, as shown in figure.

  9. Click OK and press Esc to cancel the change.

Consider the following table your data validation "cheat sheet." It contains samples of the most common types of . Feel free to copy, modify, or mix and match these examples to create your own validation rules.

Data Validation Examples

Validation Rule Description

<100

Must be less than 100.

<=100

Must be less than or equal to 100.

Between 1 and 10

Must be between 1 and 10.

0

Must not equal 0.

<1/1/95

Must be a date before 1/1/95.

>= Date( )

Must be today's date or later.

<= Date( )

Must be today's date or earlier.

"Business" Or "Pleasure" Or "Other"

Must be "Business" or "Pleasure" or "Other."

Like "??"

Must have two characters.

Like "####"

Must have four numbers.


TO VALIDATE FIELD DATA:

  1. MAKE SURE THE TABLE IS DISPLAYED IN DESIGN VIEW AND CLICK THE FIELD YOU WANT TO APPLY A VALIDATION RULE TO.

  2. CLICK THE VALIDATION RULE BOX IN THE FIELD PROPERTIES SECTION.

  3. ENTER AN EXPRESSION YOU WANT TO USE TO VALIDATE THE FIELD'S DATA.

  4. CLICK THE VALIDATION TEXT BOX IN THE FIELD PROPERTIES SECTION.

  5. TYPE THE TEXT THAT ACCESS WILL DISPLAY WHEN THE USER TRIES TO ENTER INCORRECT DATA FOR THE FIELD.