Validation and Data Integrity

Field-Level Validation

In a field's Design view properties set Validation Rule and Validation Text:

Rule:  > 0            Text: "Amount must be positive"
Rule:  <= Date()     Text: "Future dates not allowed"
Rule:  Like "A*"     Text: "Must begin with A"

Table Validation

In Table Design, the Validation area of the properties sheet checks across fields, e.g. EndDate must be after StartDate:

Rule: [EndDate] > [StartDate]

Lookups and Combobox Rules

• Give fields Lookup settings (field property) so entry uses a combo box.
• Restrict characters: an input mask for phone/EAN codes - (0000) 000-000.
• Required fields keep incomplete records out of the data.

Indexes for Integrity and Speed

• Index the foreign keys used in joins and lookups.
• Mark them Unique when the value must not repeat (e.g. Email).
• Indexed lookup fields make combo boxes instantly responsive.

Check Constraints in Practice

• Validate at entry (forms) AND at the field (tables) - two layers of defense.
• Test your rules with genuine bad data before going live.
• Keep error messages that tell the user precisely what is wrong.

Key Points

• Rules at the table level are the final gate - no bad data slips through.
• Indexes speed lookups; unique indexes enforce uniqueness.
• Clear Validation Text beats cryptic system errors.