Validation Rule Syntax

[mvital]

Hi All,

I have a table with two dates. One is a request date and the other is a authorized date. I am trying to set up a validation rule in the table design view. Here are the rules. The authorized date can be null. The authorized date occurs later (chronologically) than the request date. I can't seem to get the syntax to work. I keep geting the message, "Invalid SQL syntax cannot use multiple columns in a column level CHECK constraint." The two fields have a data/time data type. The request date should be earlier than the authorized date by the same token.

So, what I did was I entered in the dateauth field in the validation rule: is null or [datereq]>[dateauth].

I would appreciate it feedback very much!

MV

 

[Golom]

You must use constant values to validate fields. For example

[blue]< #05/27/2006#[/blue]

is an acceptable constraint but

[blue]< [SomeOtherField][/blue]

is not.

As it says in the ValidationRule Property documentation

... In addition, field validation rules can't contain references to other fields ...

 

[mvital]

I Golom,

Thanks so much for your reply. I have found some stuff online and on MS website and you can use field names. They show an example where they use a date and a date2 field and in the validation rule they have entered [date]>[date2]. So, I am confused.

I will keep searching.

thanks again!

 

[Golom]

I can believe it with respect to systems like SQL Server or Oracle that support Triggers.

The quote I put up is from the Access documentation on the ValidationRule Property so, until proven otherwise, I'm inclined to believe that it is beyond the capabilities of Access ... at least Access 2002 that I'm using. It's possible that later releases have added this capability.

 

[mvital]

I am using Access 2003. The link to the example I am talking about above

http://support.microsoft.com/kb/311172/

It starts on item 13.

thanks again!

MV

 

[PHV]

Table validation rule:
[request date]<[authorized date] OR [authorized date] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886