Validation Rules - Table & Field level

[Jay]

I have recently been trying to find out how to limit a number field to five
digits. In the end I right-clicked the table whilst in design view and
selected table properties. So I have a validation rule within the Table
properties tab of

[Mynumberfield]<99999

My question is - How does this differ from selecting the field in design
view and setting the validation rule at field level. I'm a little puzzled
as to the difference and when to use which ?

TIA

Jason

 

[Brendan Reynolds]

Your example should work as a field validation rule. One thing that you can
do in a table validation rule that you can't do in a field validation rule
is to refer to other fields. For example, in a table validation rule you can
do something like ...

SomeField >= SomeOtherField

You can't do that in a field validation rule.

 

[Jeff Boyce]

Jason

You can have one Validation Rule per field, and one Validation Rule at the
table level.

But you may find it easier to do your validating at the form level instead.
Your form controls and the form itself have BeforeUpdate events that you can
use to do editing and validation, without (practical) limitation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

Access fires the Validation Rule of the field when you enter something into
that field. It fires the Validation Rule of the table when you go to save
the record.

So, if you set the Validation Rule for the table, the field will accept a 6-
or 7-digit number into the field (since there is no rule on the field), but
then when you go to save the record, the table won't accept it.

If you set the Validation Rule for the field to:
<= 99999
you will get the error message at the time you enter the value into the
field, i.e. you won't be able to more on to another field to enter something
until you fix the field so the rule is satisfied.

In general, you use the table's Validation Rule for comparing fields, e.g.:
[EndDate] >= [StartDate]

 

[Jamie Collins]

You can have one Validation Rule per field, and one Validation Rule at the
table level.

<picky>
The latter is not actually table-level; rather, it is record-level
(row-level).

A record-level Validation Rule can reference fields (columns) in the
same record.

What would one call a Validation Rule rule that could reference other
rows in the table (or other tables)? A table-level Validation Rule, of
course <g>. There is no such animal but Jet 4.0/Access 2007 engine has
CHECK constraints, which are truly table-level.
</picky>

Jamie.

--

 

[Jeff Boyce]

Good point!

Jeff

<picky>
The latter is not actually table-level; rather, it is record-level
(row-level).

A record-level Validation Rule can reference fields (columns) in the
same record.

What would one call a Validation Rule rule that could reference other
rows in the table (or other tables)? A table-level Validation Rule, of
course <g>. There is no such animal but Jet 4.0/Access 2007 engine has
CHECK constraints, which are truly table-level.
</picky>

Jamie.