Validation Rule Restriction

[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

 

[Ken Snell [MVP]]

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.

 

[bw]

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

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>

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

 

[John Vinson]

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?

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

 

[tina]

oh, cool! i had forgotten all about record validation at the table level.

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?

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

 

[Tim Ferguson]

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.

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

 

[Ken Snell [MVP]]

Oop! You're right!

 

[bw]

Thanks for the education everyone, and like "tina" says...Cool!

Bernie

 

[Jamie Collins]

_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.

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.

--

 

[Tim Ferguson]

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))
);

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

 

[Jamie Collins]

If only they'd make that
available to a real data access library like DAO <grinning and ducking...>!

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.

--