Creating a Record Validation Rule in Access

You can use a record validation rule to specify a condition that all valid records must satisfy. You can compare values across different fields using a record validation rule. For example, a record with two date fields might require that values of one field always precede values of the other field (e.g., StartDate is before EndDate).

1. Scenario

In a database capturing the details of books in a collection, there is a field name bookFirstPublished to record the date of the first edition of each book, as well as a field bookThisPublished to record the year of publication of the edition the collector has in his possession.

Download the data file and import it into a new table.

2. Rule

With the table in Design View, the Record Validation Rule must be entered in the table’s Property Sheet:

1. Open tblBooks in Design View
2. Identify the two fields that will be compared: bookFirstPublished & bookThisPublished
3. Activate the Table Design ribbon
4. Click on the Property Sheet command button
5. Select the Validation Rule and enter: [BookThisPublished]>=[BookFirstPublished]
6. Enter suitable Validation Text

3. Test

Test the following scenarios:

• Both fields blank
• Year in bookFirstPublished only
• Year in bookThisPublished only
• Year in bookFirstPublished lower than bookThisPublished
• Year in bookFirstPublished same as bookThisPublished
• Year in bookFirstPublished higher than bookThisPublished

References:

1. Microsoft Support (no date) Microsoft Support. Available at: https://support.microsoft.com/en-us/office/restrict-data-input-by-using-validation-rules-b91c6b15-bcd3-42c1-90bf-e3a0272e988d (Accessed: 14 October 2023).