Validation rule (2000 SR-1)

[happyspider]

I have 2 fields on a table "Date_Logged" and "Date_Cleared". I wanted to have a validation rule put in "Date_Cleared" so that it could not be before "Date_Logged". I used >="Date_Logged". For some reason no matter what combination of dates I use the validation rule fails. Am I missing anything obvious? Not sure if this is relevant but the default value of "Date_Logged" is set to today.

[HansV]

You cannot refer to other fields in the validation rule for a field. You need to set the validation rule of the table as a whole:

- Open the table in design view.
- Make sure that the Properties window is visible (View | Properties)
- In the Validation Rule property in the Properties window, enter

[Date_Cleared]>=[Date_Logged]

- You must use square brackets to refer to a field, not quotes (quotes indicate a literal text string.)
- In the Validation Text property, enter the text for the message to be displayed if the validation rule is violated.

The table-level validation rule will not be checked while the user is entering the dates, but before the record is saved, for example when the user moves to another record.

[happyspider]

Many thanks - in the main this has worked - however I now think there is a problem with the default value in [Date_Logged] which is set as =Now(). When the default value is used the validation rule fails if [Date_Cleared]=[Date_Logged]. If [Date_Cleared] is overtyped with a date then the validation rule passes if the dates are the same. Sorry to be a pain!

[HansV]

In the first place, I would set the default value for Date_Logged to Date() instead of Now(). Now has both a date and a time component. If Date_Logged is set to 15/06/04 12:09 and Date_Cleared is set to 15/06/04, the test Date_Cleared >= Date_Logged fails, for Date_Cleared corresponds to midnight.

You may have to change existing records too.

[happyspider]

Many Thanks again HansV - that has done the trick.