validation of field

[Guest]

i am using ms access 2003

i wish to use a validation rule that refers to another value in a differnt
field in the same table.

i have tried- >=[startDate], but this doesn't work.

i get the error message "invalid SQL syntax- cannot use multiple columns in
a column-level CHECK constraint"

can anybody help?

 

[Allen Browne]

For comparing fields, use the Validation Rule of the *table* instead of the
rule for the field.

In table design view, open the Properties box (View menu).
Beside the Validation Rule, enter your expression, e.g.:
[EndDate] >= [StartDate]

In some versions of Access, that would make both fields required, so you
might want to use:
([EndDate] >= [StartDate]) OR ([EndDate] Is Null) OR ([StartDate] Is
Null)

Another alternative would be to sure the duration in days instead of the end
date. That has the advantage of being correctly normalized (i.e. you don't
have an end date that is dependent on the start date), but would not be
ideal for all situations.

 

[Duane Hookom]

You can set the validation in the table properties:
Validation Rule: IIf(IsNull([EndDate]),-1,[EndDate]>=[StartDate])
Validation Text: The End must be later than the Start

 

[peregenem]

In some versions of Access, that would make both fields required, so you
might want to use:
([EndDate] >= [StartDate]) OR ([EndDate] Is Null) OR ([StartDate] Is
Null)

Could you elaborate please i.e. which versions of Jet/Access?

Another alternative would be to sure the duration in days instead of the end
date. That has the advantage of being correctly normalized

I don't know what you mean by 'correctly normalized' (longhand for 3NF
or intentionally ambiguous?) but SQL was designed to model durations
using a start_date and end_date pair. Google for it e.g.

SQL ISO "half-open intervals"