A validation rule is a condition that data must satisfy before Access accepts it. If the entered value does not satisfy the condition, Access rejects it.

Example:

>=0 And <=100

This rule allows numbers from 0 to 100, inclusive. Values such as 50 and 100 are accepted, while 120 is rejected.

Validation rules are useful for:

• Checking student marks.
• Preventing negative fees.
• Restricting dates.
• Checking quantities.
• Maintaining consistent data entry.

1. Open your database in Microsoft Access.
2. Right-click the required table in the Navigation Pane.
3. Select Design View.
4. Select the field that needs validation.
5. Locate the Field Properties section.
6. Find the Validation Rule property.
7. Enter the required condition.
8. Optionally enter a message in Validation Text.
9. Save the table.

Field-level validation rules check individual fields. Table-level validation rules can compare multiple fields in the same record.

Suppose a Students table contains a Number field named Marks. Marks must be between 0 and 100.

Validation Rule:

>=0 And <=100

Validation Text:

Enter marks between 0 and 100.

Suppose a CourseFee field stores the fee amount. Negative fees are not allowed.

Validation Rule:

>=0

Validation Text:

Course fee cannot be negative.

Examples:

• ₹0 — Accepted.
• ₹5,000 — Accepted.
• -₹500 — Rejected.

Use the Currency data type for a monetary field and configure the validation rule in its Field Properties.

Suppose you want to accept ages from 18 to 60.

Validation Rule:

>=18 And <=60

Validation Text:

Age must be between 18 and 60.

This rule accepts 18, 25, and 60. It rejects 17 and 61.

Use this example only when those age limits match the actual requirements of your application.

Suppose an inventory table contains a Quantity field. The quantity must be greater than zero.

Validation Rule:

>0

Validation Text:

Quantity must be greater than zero.

This rule accepts 1, 5, and 100, but rejects 0 and negative values.

If your application needs to allow zero quantity, use >=0 instead.

Validation rules can restrict dates according to your database requirements.

Example: Admission Date Cannot Be in the Future

Validation Rule:

<=Date()

Validation Text:

Admission date cannot be in the future.

The Date() function returns the current date. This rule is suitable when future admission dates are not permitted.

Example: Date Must Be on or After 1 January 2026

Validation Rule:

>=#1/1/2026#

The date literal syntax and displayed date format can depend on Access settings. Test date rules using the regional configuration of your database.

AND Operator

AND requires both conditions to be true.

Example: Marks from 0 to 100.

>=0 And <=100

OR Operator

OR accepts a value when at least one condition is true.

Example: Discount must be 0 or 10.

=0 Or =10

This rule accepts only the numeric values 0 and 10. It rejects values such as 5 and 15.

Another example:

<10 Or >90

This rule accepts values below 10 or above 90. Values from 10 through 90 are rejected.

Text validation can restrict the values accepted in a Short Text field.

Example: Course Must Be ADCA or Tally

In ("ADCA","Tally")

This rule accepts ADCA or Tally as the field value.

Example: Code Must Not Be Empty

Is Not Null

This checks that a field contains a non-Null value. If you also need to prevent empty text strings, consider the field's Required property and an appropriate validation rule for your design.

Use validation expressions appropriate for the field's data type and test them with actual data-entry scenarios.

Example: End Date Must Not Be Before Start Date

Suppose a table has fields named StartDate and EndDate. A table-level validation rule can compare them:

[EndDate]>=[StartDate]

This rule checks that EndDate is on or after StartDate. Configure it as a table-level rule, not as a rule belonging only to one of those fields.

Both properties can be used together. For example, a Marks field can be required and also restricted to values between 0 and 100.

Create a table named Students with these fields:

Tasks:

1. Create the table in Design View.
2. Enter the validation rules for the appropriate fields.
3. Add suitable Validation Text messages.
4. Save the table.
5. Enter valid marks such as 75.
6. Try entering marks such as 120.
7. Try entering a negative course fee.
8. Observe the validation messages when rules are violated.

1. What is a validation rule?
2. Where can you set a field-level validation rule?
3. Write a rule that allows marks from 0 to 100.
4. Write a rule that prevents negative course fees.
5. What is Validation Text?
6. What is the difference between AND and OR?
7. What is the difference between field-level and table-level validation?
8. Write a rule that restricts a discount to 0 or 10.
9. How does Required differ from Validation Rule?
10. Why should validation rules be tested before using a database?