validation rule dependent upon another field

[Guest]

hello there,

i have a field called 'time' of datatype date/time, short time, and a field
called 'date' of datatype date/time, short date. I wanted to validate the
time field which will be dependent upon the date field.

The validation rule I require is:
Assuming 'date' field contains a value, if the day of the week of the date
specified is monday to friday only allow the 'time' field to contain times
between 0800 and 2100. If the day of the week of the date specified is
saturday only allown the 'time' field to contain times between 0800 and 1500.

I am not sure how to write the validation rule for the 'time' field or if it
is at all possible.

Thanks for your time

JASON

 

[Steve Schapel]

Jason,

First of all, I would recommend you re-name the fields. Both 'date' and
'time' are Reserved Words (i.e. have a special meaning) in Access, and
as such should not be used as the names of fields or controls.

In design view of the table, select Properties from the View menu.
Then, in the Validation Rule property, you can put something like this...
[TheDate] Is Null Or ((WeekDay([TheDate]) Between 2 And 6) And
([TheTime] Between #08:00# And #21:00#)) Or ((WeekDay([TheDate])=7) And
([TheTime] Between #08:00# And #15:00#))

Well, I didn't test this, but I think it is right. Note that this
validation check will take place at the point where the record is being
saved. If you want it to take place at another time, such as when the
'Time' data is entered, then you will need to validate it in code, or
else force a save of the record on the After Update event of the TheTime
control on the form.