Table Validation Rule

[Max]

I've got a table with the following fields:
Week, Data1, Data2, WeekDayofData1, WeekDayofData2

I want to make a table validation rule that if the Data1
field is filled, then WeekDayofData1 must also be filled.
And the same for Data2 and WeekDayofData2. It is however
ok, for only the Week field to be filled in with none of
the other fields filled.

I can't seem to get anything to work w/ the expression
builder and the Null function and I couldn't find any good
examples to work from.

Any help?

Max

 

[Cheryl Fischer]

Max,

When creating validation rules at the table design level, you cannot make a
field validation rule which references another field, according to the Help
file. (To see the full text of this help topic, click on the Validation
Rule property in table design and then press F1.)

What you can do is create your validation rules in VBA in a Form's events:
Before Insert for a new record or Before Update for an existing record which
is being edited.

 

[Max]

Cheryl,

I had read the Help as well...which I quote below:
"In addition, field validation rules can't contain
references to other fields. For records, expressions can
include references to fields in that table."

For this reason, I was thinking that I could do validation
at the record level (instead of at the field level), thus
requiring a Table Validation Rule.

Am I missing something here? It still seems like I should
be able to validate this w/o resorting to VBA.

Max

 

[Jeff Boyce]

Max

Yes... and No! It is possible to establish a validation rule on a table
(use Properties). However, it is possible to establish only ONE validation
rule on a table (just like you can use a field's properties to establish a
single validation rule on the field.

If there are multiple validation checks you need to make on the table, use a
form! By adding a few simple lines of code in the BeforeUpdate event of a
form bound to your table, you can check ALL the validation rules you wish.
Besides, tables are for storing data, forms for displaying it -- you and
your users need to be working in forms anyway!

JOPO (just one person's opinion)

 

[Cheryl Fischer]

You can open your table in design view and then click ALT-Enter. This opens
a Table Properties sheet where there is a Validation Rule property. You can
try getting all of your rules into that property.

 

[Tim Ferguson]

I want to make a table validation rule that if the Data1
field is filled, then WeekDayofData1 must also be filled.
And the same for Data2 and WeekDayofData2. It is however
ok, for only the Week field to be filled in with none of
the other fields filled.

Put this in the Table-level validation rule; it has to go all on one line
into the box, so it's easier to type than to read!


((Data1 IS NULL)=(WeekDayOfData1 IS NULL)) AND
((Data2 IS NULL)=(WeekDayOfData2 IS NULL))


Hope that helps


Tim F