Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2004
    Location
    Lancashire, England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation rule (2000 SR-1)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation rule (2000 SR-1)

    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.

  3. #3
    New Lounger
    Join Date
    May 2004
    Location
    Lancashire, England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule (2000 SR-1)

    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!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation rule (2000 SR-1)

    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.

  5. #5
    New Lounger
    Join Date
    May 2004
    Location
    Lancashire, England
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation rule (2000 SR-1)

    Many Thanks again HansV - that has done the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •