Validation Rule

  • Thread starter Thread starter gford
  • Start date Start date
G

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.
 
[email protected] said:
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.
Click to expand...

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.
 
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
 
Back
Top