Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    80

    Unanswered: Validation rules not working for dates and times

    For the validation rules in table design view, I need to validate that the value entered in the TimeOrdered field is between 8:00AM and 9:00PM. My Date/Time format is Medium Date, and the field's default value is =Time().

    I tried >#8:00AM# < #9:00PM, but that didn't work. When I click off the Validation Rule field, even though the time format is set to Medium Time, the code automatically converts to >#8:00:00AM# < #9:00:00PM#.

    I also need something similar for DateOrdered - to validate that the date entered is not before 08/08/1996, and that it is not after the current date (just in case somebody tries to change the default value). This format is Long Date, and the default value is =Date().

    This needs to be implemented using the table design view validation rule box for simplicity's sake.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hello again

    Check your format - big difference between medium date and medium time.
    Don't worry about the converting it does to #9:00:00PM# - it is just accounting for seconds too. Your format only affects the display of the field values not the display of any validation rules.
    Have you tried: >#8:00AM# AND < #9:00PM# ?
    Once you get your time validation working you can extend the principal to the date field easy peasy
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    80

    Thanks :D

    Thanks for your replies...they're very helpful to me. I'm enjoying this extending-myself-in-Access bit, but it's so confusing and tiring and very very frustrating.

    But thankyou, have a good one.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Grace

    Tried to send you a screen dump of my fully functioning, validating and defaulting table design but couldn't! However, it is no different to what I descibed. Gist is, double check what I put in the post and paste the following into the validation rule (not validation text) entry:

    >#08:00:00# And <#21:00:00#

    As far as the default value is concerned, the database behaves in the same way as if you had entered the time yourself. If you enter a record between 08:00Am and 21:00PM and don't change the entry then the data is valid. If you enter a record outside these times, or change the time entry then you should get a message telling you that "one or more values are prohibited by the validation rule '>#08:00:00# And <#21:00:00#' set for ....".
    Anyway, have a go and let me know how it goes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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