Search code examples
ms-accessdatatablescheck-constraints

MS-Access 365 Table Level - Date Validation per Row

Working in MS Access 365 I am trying to set constraints on a table of appointment cycles. I was wondering how I can have the column CycleStartDate always be less than or equal to CycleEndDate; and conversely ensure that CycleEndDate always be greater than or equal to CycleStartDate?

Also, as a bonus I only want to consider the date portion of the fields, I do not want to consider the TIME values. Ideally I would want to set CycleStartDate to always be 00:00 (12:00 AM) of the date selected and CycleEndDate to be 23:59 (11:59 PM) of the date selected.

I had tried to use the Validation Rule in Field Properties on CycleStartDate of [CycleStartDate]<=[CycleEndDate] and received the message "Invalid SQL Syntax - cannot use multiple columns in a column level Check Constraint"

Solution

  • A field validation rule can not reference another field. But a record validation rule can reference more than one field in the same record (see doc).

    I tested this one in my table and I believe it does what you want:

    [CycleStartDate]<=[CycleEndDate]