Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Data Validation (Access 2000)

    I have a date field in a table formatted dd/mm/yyyy and I need to restrict entry to dates equal to the last day of a Quarter of any year. e.g. 31/03/2003 or 30/06/2004 or 30/09/2002 or 31/12/2005. If someone can please enlighten me on how to do this, I would really appreciate it. Thanks Raymond

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

    Re: Data Validation (Access 2000)

    Say that your field is named MyDate. Set the Validation Rule property of the field to

    (Month([MyDate]) Mod 3=0) And (Day([MyDate]+1)=1)

    The first part tests whether the month number is divisible by 3 (the Mod = modulo function returns the remainder of the month number after division by 3.) The second part adds one day to the date and tests whether the day number is 1 (if you enter the last day of any month, the next day must be the first of the next month; this is much easier than trying to compute whether the month has 30 or 31 days.)

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Data Validation (Access 2000)

    Thanks Hans, it worked like a charm.

    Raymond

Posting Permissions

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