Validation Rule Restriction

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

What I want for a Validation Rule is...[CompleteDate]<=[BillDate]
And of course, I get the error message..."Invalid SQL syntax - cannot use
multiple columns in a column-level CHECK constraint."

I have code in my form to accomplish this same thing, but I have been
thinking that it doesn't sound reasonable that we can't perform this kind of
check in the table.

So my question...is there away around this, or different coding in the
table, or different syntax, or something that will allow me to validate data
like this in the table?

Thanks,
Bernie
 
Table-level validation cannot refer to another field in the same table, nor
to a field in another table. To do what you wish, you'll need to enforce it
on the form itself, which is what it sounds like you've already done.
 
OK, thanks Ken.

I realize the error message meant that, but I thought I'd take a chance that
there might be a work-around.

Thanks,
Bernie


Ken Snell said:
Table-level validation cannot refer to another field in the same table, nor
to a field in another table. To do what you wish, you'll need to enforce it
on the form itself, which is what it sounds like you've already done.

--

Ken Snell
<MS ACCESS MVP>

bw said:
What I want for a Validation Rule is...[CompleteDate]<=[BillDate]
And of course, I get the error message..."Invalid SQL syntax - cannot use
multiple columns in a column-level CHECK constraint."

I have code in my form to accomplish this same thing, but I have been
thinking that it doesn't sound reasonable that we can't perform this
Click to expand...
kind
of
check in the table.

So my question...is there away around this, or different coding in the
table, or different syntax, or something that will allow me to validate data
like this in the table?

Thanks,
Bernie
Click to expand...
Click to expand...
 
What I want for a Validation Rule is...[CompleteDate]<=[BillDate]
And of course, I get the error message..."Invalid SQL syntax - cannot use
multiple columns in a column-level CHECK constraint."

I have code in my form to accomplish this same thing, but I have been
thinking that it doesn't sound reasonable that we can't perform this kind of
check in the table.

So my question...is there away around this, or different coding in the
table, or different syntax, or something that will allow me to validate data
like this in the table?
Click to expand...

You *can* in fact put this validation rule in the Table's validation
rule property (though you cannot put it in a field validation
property).

In my experience table level validation gets pretty complicated
quickly if you have more than one rule; you need a logical expression
which evaluates to True or False, true if the record is valid. This
expression can get very hairy.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
oh, cool! i had forgotten all about record validation at the table level.
:)


John Vinson said:
What I want for a Validation Rule is...[CompleteDate]<=[BillDate]
And of course, I get the error message..."Invalid SQL syntax - cannot use
multiple columns in a column-level CHECK constraint."

I have code in my form to accomplish this same thing, but I have been
thinking that it doesn't sound reasonable that we can't perform this kind of
check in the table.

So my question...is there away around this, or different coding in the
table, or different syntax, or something that will allow me to validate data
like this in the table?
Click to expand...

You *can* in fact put this validation rule in the Table's validation
rule property (though you cannot put it in a field validation
property).

In my experience table level validation gets pretty complicated
quickly if you have more than one rule; you need a logical expression
which evaluates to True or False, true if the record is valid. This
expression can get very hairy.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Table-level validation cannot refer to another field in the same table,
nor
to a field in another table. To do what you wish, you'll need to enforce
it
on the form itself, which is what it sounds like you've already done.
Click to expand...

More coffee and/ or sleep required again, I think Ken! _Table_ level
validation is exactly for this sort of check, but the oP had the condition
in the column level validationrule.

Of course you are right about fields in other tables.

B Wishes


Tim F
 
Tim Ferguson said:
_Table_ level
validation is exactly for this sort of check, but the oP had the condition
in the column level validationrule.

Of course you are right about fields in other tables.
Click to expand...

I don't think he is. See:

ACC2000: How to Create a Jet CHECK Constraint
http://support.microsoft.com/default.aspx?scid=kb;en-us;201888

The exmaple cited is:

CREATE TABLE Customers (CustId IDENTITY (100, 10),
CFrstNm VARCHAR(10),
CLstNm VARCHAR(15),
CustomerLimit DOUBLE,
CHECK (CustomerLimit <= (SELECT SUM (CreditLimit) FROM CreditLimit))
);

Jamie.

--
 
I don't think he is. See:

ACC2000: How to Create a Jet CHECK Constraint
http://support.microsoft.com/default.aspx?scid=kb;en-us;201888

The exmaple cited is:

CREATE TABLE Customers (CustId IDENTITY (100, 10),
CFrstNm VARCHAR(10),
CLstNm VARCHAR(15),
CustomerLimit DOUBLE,
CHECK (CustomerLimit <= (SELECT SUM (CreditLimit) FROM CreditLimit))
);
Click to expand...

That's very interesting, and I did not know that. If only they'd make that
available to a real data access library like DAO <grinning and ducking...>!

All the best


Tim F
 
Tim Ferguson said:
If only they'd make that
available to a real data access library like DAO <grinning and ducking...>!
Click to expand...

Surely even committed DAO dabblers can be occasionally be tempted over
to the ADO side if such goodies are on offer <g>?

Anyhow, AFAIK this syntax can now be used natively from Access2003.

Jamie.

--
 
Back
Top