INVALID SQL SYNTAX on Validation Rule

[CharlesMTF]

New to the list, and hoping I can get some help on a few issues I'm having in Access 2003.

I have a table where two fields represent log numbers. The fields are called "LogNo" and "LogNoPct". On occasion, the person entering the data will accidentally place the same number into both fields, which is clearly a mistake. One record should never have the same log number in both fields. So, I set up a validation rule for "LogNoPct". I just want it to check the last 4 digits of the log number and not allow the entry if both have the same last 4 digits. In the validation rule for "LogNoPct" I have this:

Right([LogNo],4)<>Right([LogNoPct],4)

Everything looks good until I try to save the changes to the table's design. When I hit SAVE I get this error:

Invalid SQL Syntax - cannot use multiple columns
in a column-level CHECK constraint.

Have no idea why its giving me this error. Any help is greatly appreciated.

 

[Smart]

If I remember correctly.

When using column constraints in a table you can't include other columns in the constraint,i am sure if I am wrong someone will put me right.

You could place the validation code in the after update of the LogNoPct field

 

[CharlesMTF]

Actually... I figured out the problem. Or, at least I figured out what I was doing wrong. I was putting the validation in a field-level validation. Like you said in your post, column constraint can't include other columns in the constraint. The solution was to make it a record-level validation. I know I'm probably using the wrong terminology. But, basically, instead of picking the field and doing PROPERTIES, I went to the properties of the entire table, and added the validation there, and then it worked. So, now it checks the validation after the entire record is entered and I hit SAVE.

Thank you for the input. Much appreciated.

 

[ReneeO]

CharlesMTF, thanks for posting back about your solution - it has helped me immensely!