In MS Access, how to write validation in design view for ensuring 2nd date field greater than first date field

Question

My field is labeled FromDate and the first possible date is 2/1/2004. In design view the FromDate field has in the validation slot >=#2/1/2004# so nothing gets entered less than 2/1/2004 in datasheet view. The problem I have is the next field. It is labeled ThruDate. This ThruDate cannot be less, prior to, the FromDate. I would like to validate the ThruDate field. It takes >=#2/1/2004# but I also need it to prevent any dates prior to a date which is, for ie, 3/1/2006? Please advise. thanks.

My FromDate field is constantly moving up in time, 5 rows later it will be, for ie, 4/1/2004, so the next field, thruDate must be later that 4/1/2004. so the syntax of >=#2/1/2004# does the job to prevent dates in the ThruDate to be prior to 2/1/2004, but it will not prevent dates from being prior to a NEW FromDate such as 4/1/2004 and whatever NEWER FromDate I may input. [i should have mentioned at the outset that my FromDate does increase periodically] I tried >=#2/1/2004 And > [FromSomeDate] but it didn't take. please advise if possible. thanks

Answer 1

If the other date is in another control (such as a Text Box) on the same form, try >=#2/1/2004# And >=[txtSomeDate] for the Validation Rule (where txtSomeDate is the name of the other control).

That should work as long as txtSomeDate is Not Null (or empty string) AND both FromDate and txtSomeDate are valid dates.

If you want anything more complex than that, it might be better to use the AfterUpdate event of FromDate to write code that handles more complex conditions.

§