Validation rule build on other field in the same table

[tc2004]

How can I build a validation rule for one field in a table based on another
field?

Here is the situation: I have an entry date and the date for a test that has
to have occurred within 12 months prior to the entry date. If the date of
test is more than 12 months prior to the entry date, data entry should not be
permitted. Is there a way to do that, or can this only be done on the data
entry form?

Thanks.

 

[SuzyQ]

on the before update event of the field you are validating...

if me.testdate < dateadd('y',me.entrydate,-1) then
cancel = true
endif

 

[Dirk Goldgar]

How can I build a validation rule for one field in a table based on
another
field?

Here is the situation: I have an entry date and the date for a test that
has
to have occurred within 12 months prior to the entry date. If the date of
test is more than 12 months prior to the entry date, data entry should not
be
permitted. Is there a way to do that, or can this only be done on the
data
entry form?

You can set a table-level validation rule that will prohibit the saving of a
record if the test date is more than 12 months before the entry date. It
would look something like this:

[TestDate]>DateAdd("m",-12,[EntryDate])

Note, though, that this rule will only be applied when the user attempts to
save the record. If you want to validate the date as it is entered into the
control on the form, you'll have to use the BeforeUpdate event of the
control, and not a validation rule. Of course, you could use both, just to
make sure.

 

[tc2004]

This refers to the form, correct? There is no way of doing this in the
table, is there?

 

[Jeff Boyce]

Why do you ask?

That is, if your application has forms for data entry/display (and it
SHOULD!), there's no good reason to be working directly in the tables.

That said, if your table is being used by more than one application, you'll
need to decide if you want to embed that "business rule" directly in the
table, or in each form that uses that table's data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SuzyQ]

I reversed my parameters on the dateadd function the last two parameters
should be reversed.

Yes the code I wrote was for the form. Dick Goldgar provided a rule for the
table.