Validation Rules in Tables

[jujubean]

jujubean Sep 27, 1:22 pm show options

Newsgroups: comp.databases.ms-access
From: "jujubean" <[email protected]> - Find messages by this author
Date: 27 Sep 2005 11:22:36 -0700
Local: Tues, Sep 27 2005 1:22 pm
Subject: validation rules
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I have a table which is populated using paste append from excel. I
want to allow records only if the value of one field matches one of
many values in a field in another table. I tried to set the validation
rule In([TableName].[FieldName]) where TableName is the table
containing the valid values and FieldName is the field containing those
values. I get an error message stating the field could not be found.

Is my syntax wrong with the In function?

Any suggestions?

 

[KARL DEWEY]

Try bringing data into a temp table then use an append query joined on your
validation table.

 

[Van T. Dinh]

You cannot use Field validation or Table validation in this case as Field
validation refers to a particular Field only and Record/Table validation can
only refer to Fields in the *same* Table.

You can probably create a relationship and enforce Referential Integrity but
I am not sure whether you actually want ot use R.I. because it should
represent the real-life relationship.

If you only want to import those Excel rows that meet this condition then
you should simply link the Excel spreadsheet to your database then use this
linked Excel "Table" and you validation "source" Table in a Make-Table Query
to create a Table with only Records that satisfy your condition.

Check Access Help topic "About restricting or validating data".