Validation Rule

[gford]

Trying to compare two fields called end dates and start
date with a validation rule. I get a SQL syntax error -
"cannot use muliple columns in a column-level CHECK
constraint." I want the valiation rule to prevent me from
entering an end date that is greater than the start date.

 

[Dirk Goldgar]

Trying to compare two fields called end dates and start
date with a validation rule. I get a SQL syntax error -
"cannot use muliple columns in a column-level CHECK
constraint." I want the valiation rule to prevent me from
entering an end date that is greater than the start date.

Field validation rules can't refer to other fields in the table. You
must use a table validation rule for this. With the table open in
design view, click the Properties button to open the table's property
sheet, and enter a validation rule along these lines:

[EndDate]>=[StartDate] Or [EndDate] Is Null

(using your own field names, of course). Note that I've assumed (a) you
want to prevent the entry of an end date that is *less than*, not
greater than, the start date, and (b) you will allow a record to be
saved that has no end date yet. If those assumptions are wrong, adjust
the validation rule accordingly.

 

[Dave Ruhl]

I use this in the StartDate LostFocus Event -

If DateDiff("d", Me.StartDate, Me.EndDate) < 0 Then
MsgBox ("Start Date cannot occur after Date Reported")
Me.StartDate.SetFocus
End If