Getting the validation property to work in a table

[usr_X]

I have two columns in a table: StartDate and EndDate. Using the validation property in table design view I wish to make sure the date entered in the EndDate field cannot be a date occurring before or equal to the date in the StartDate field. However, I am unable to get this to work. I have tried "> StartDate" and "> [StartDate]" (without quotes) in the validation property but to no avail.

Thank you in advance for your help.

 

[Mr. B]

usr X,

The following is copied from the Access Help file:

For field and record validation rules, the expression can't contain user-defined functions, domain aggregate or aggregate functions, the Eval function, or CurrentUser method, or references to forms, queries, or tables. In addition, field validation rules can't contain references to other fields.

You will need to handle this type of validation from within your form not at the table level.

 

[usr_X]

Mr. B, thank you for your help.

I previously read the the information you referenced in the Access help file. However, from the Microsoft Office Access 2007 Bible (Wiley publishing), it says: "...a validation rule applied to a record in a table can reference fields in the same table (a record-level validation rule is set in the table's property sheet, rather than on an individual field.)", pg. 66. Unfortunately, it doesn't tell me how to do this.

With further investigation in the Access help file I found the procedure for establishing a record-level, as opposed to a field-level, validation rule. However, the procedure is exactly the same as a field-level validation rule... and therefore obviously doesn't work. If you'd like to see this reference, type "validate data in a record" in Access help under "Content from this computer" and then choose the "Validate data during entry in table fields" link.

Apparently there is a way to validate data at the table-level based on a value of another field in that table, but there's no accurate description I can find on how to do that.

If anyone knows how to do this, please advise.

 

[Mr. B]

I typed in the search statement you provided and found that there is a link that describes how to "Define a validation rule to control when a record can be saved"

With your table in design view, place your cursor in the Title bar at the top and right click and select Properties. This will display the Properties dialog box for the Table not just one field.

In testing this, if I place "[EndDate]>=[StartDate]" in the validataion Rule property then I can enter a date in the Start Date and a date in the End Date. I can enter a date in the End Date that is acutally less than the Start Date but when I attempt to move off that record, I will be prompted with the text that I provided in the Validation Text property and the record cannot be saved until the issue is corrected with a date that is equal to or greater than the Start Date.

HTH

 

[usr_X]

I am glad to say this is what I was looking for: the ability to create a table-level validation based on field values in the table. The key was getting to the table's property sheet to set the validation rule there.

I am less enthused about the Access help system. Searching the help system for "table property sheet" or variations of "Define a validation rule to control when a record can be saved" still doesn't bring up anything useful (I don't know where/how you were able to find the latter).

Thanks again for your help.

Sincerely,

Usr_X

 

[Mr. B]

urs_X,

In the Help window click on the "Answer Wizard" and type in "validate or restrict data entry" and click "Search". take a look at the second link:
"Define a validation rule to control when a record can be saved"

That's where I found it. Sometimes you just have to type in a exact phrase. I got lucky.

 

[khawar]

By the method told by Mr B you can add just one validation for table

Access tables support check constraints by which you can add more than one constraints for every column in table in which you can validate data from current table or any other table

But this can be done only by using vba (as far as I know)

These constraints are of a great use

 

[khawar]

I have attached a sample which have some table constraints to give you an idea what you can do with them

 

[usr_X]

Khawar, thank you very much for these files. These will come in very useful. I am glad to have found this forum.

Sincerely,

usr_X

 

[dah_axs]

I have downloaded the sample file, but cannot find the MyConstraint1 code/ expression. I am in access 2000. Thank You

 

[khawar]

You cannot view constraint you have to add or drop constraint in code using ADO

You can refer to this thread where I have provided code to Add or Drop constrainsts and there is also some more useful information

 

[dah_axs]

Thank you, I am learning, but not quite there. I have the code below, but my syntax seems to be off and I do not know how to add an allow Null values exception. I am entering this in the Immediate window.
DoCmd.RunSQL "ALTER TABLE tblmasterlocation ADD CONSTRAINT item_ck_constraint CHECK (ITEM In tblPriceFile(ITEM))"

Thank You

 

[khawar]

You cannot do this through sql you have to use ado in vba moreover syntax of your check is not appropriate