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.
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
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.