Table-Level Validation

[Andrew]

Hi All,

I have a quick question. I hope someone can help.

I have an access database with a tbale in there called "DM Patient Tracking".

In this table there are 3 fields called [Unreviewed Discrepancies], [Passive
Review Discrepancies], and [Inv Review Discrepancies], all of which are
numeric fileds with default values of 0.

I have a forth field call [Data Review Complete] which I want populated (no
user input) with a logical "Yes/No" value as the field is set up that way.
This field also have a default value of "No".

I have loaded the following code into the Validation Rule in the Table
Properties.

"IIf(Eval([Unreviewed Discrepancies]=0 And [Passive Discrepancies]=0 And
[Inv Review Discrepancies]=0),[Data Review Complete]="Yes",[Data Review
Complete]="No")"

What I am getting, when I try to run the Test Validation Rules is an error,
as below.

"Unknown function 'Eval' in validation expression or default value on 'DM
Patient Tracking'.

What is wrong here. the code looks sound, and the rule for using IIF and
comparison operators (And, Or), is to use the Eval() function.

Please help.

 

[John W. Vinson]

Hi All,

I have a quick question. I hope someone can help.

I have an access database with a tbale in there called "DM Patient Tracking".

In this table there are 3 fields called [Unreviewed Discrepancies], [Passive
Review Discrepancies], and [Inv Review Discrepancies], all of which are
numeric fileds with default values of 0.

I have a forth field call [Data Review Complete] which I want populated (no
user input) with a logical "Yes/No" value as the field is set up that way.
This field also have a default value of "No".

I have loaded the following code into the Validation Rule in the Table
Properties.

"IIf(Eval([Unreviewed Discrepancies]=0 And [Passive Discrepancies]=0 And
[Inv Review Discrepancies]=0),[Data Review Complete]="Yes",[Data Review
Complete]="No")"

What I am getting, when I try to run the Test Validation Rules is an error,
as below.

"Unknown function 'Eval' in validation expression or default value on 'DM
Patient Tracking'.

What is wrong here. the code looks sound, and the rule for using IIF and
comparison operators (And, Or), is to use the Eval() function.

Several errors here! A Validation Rule must evaluate to either TRUE (-1) or
FALSE (0) based on fields within the table. It's not a text string such as
"No", and you would not use an IIF or other function in it.

If the meaning of [Data Review Complete] is just that there are no zero values
in the other three fields then the field should simply *not exist* in your
table, as its value depends on these other fields. Instead you would calculate
it on the fly in a Query:

[Data Review Complete]: ([Unreviewed Discrepancies] = 0 AND [Passive Review
Discrepancies] = 0 AND [Inv Review Discrepancies] = 0)

This expression will be TRUE if all three fields are 0, FALSE if any is
nonzero.

 

[Jeff Boyce]

Andrew

From your description, this would be a very sensible approach ... if you
were working with a spreadsheet!

Since you're working with Access, a relational database, a more appropriate
approach would be to use a query to calculate the value of a new 'field' you
create (ONLY) in the query ... I suppose you could call it something like:

[Data Review Complete]

and "calculate" a Yes/No value using an IIF() statement.

The obvious advantage is that every time you run the query, the calculation
determines the value of [Data Review Complete].

The obvious problem with trying to store that in an Access table is that
there are no "Triggers" that would tell Access to re-calculate and store the
value when someone updates one of the three component fields.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Note: if you, perchance, are storing your data in a 'back-end' that DOES
have triggers on tables, like SQL-Server, you could decide to store that
calculated value ... but check with your local DBAs first to see if they're
willing to denormalize the table structure for that.