By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,412 Members | 2,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

Creating a table-level validation rule to compare date fields

P: 19
Hello - I am in a class for Access 2007 Database apllication design and we are covering types of Validation rules. We have a problem that I'm trying to solve but have no idea how to go about writing it. We are building a database for a condo rental company and the rental transaction table includes an arrival date and a departure date. We need to write a table-level validation rule that indicates the departure date is seven days after the arrival date. I have been trying to figure out how to write this but I can't find any examples for this type of VR and so I don't know what direction to go. I have found that these VR use And/Or statements but my brain is not seeing how to use these statements for this problem. Can anyone give me an example or lead me to a reference that will show me information on Table-level validation rules that compare date fields?

Thank you
Laura Edmark
Oct 14 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Open the table in Design View and right click anywhere and go to properties.

This should open the table properties window.

In Validation Rule put ...

[departure Date]>=[arrival Date]+7

Change the field names to correspond to your own exactly
Oct 14 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
The rule is basically if you are putting a validation rule on that just involves one field you can put it at field validation level. However, if the rule involves a comparison to another field you need to put it at Table validation level.
Oct 14 '07 #3

P: 19
Thank you so much for your help and direction. I'll give it a try.

Laura


Open the table in Design View and right click anywhere and go to properties.

This should open the table properties window.

In Validation Rule put ...

[departure Date]>=[arrival Date]+7

Change the field names to correspond to your own exactly
Oct 15 '07 #4

P: 19
OK - I went into the table properties and entered the validation rule for the dates and then when I went back into the table to test the data nothing happened - it let me enter a rental period of more than seven days and didn't give me the validartion tecxt like it should have - it let me save the entry and everything. Am I missing something?

Thanks
Laura Edmark
Oct 15 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Open the table in Design View and right click anywhere and go to properties.

This should open the table properties window.

In Validation Rule put ...

[departure Date]>=[arrival Date]+7

Change the field names to correspond to your own exactly
Sorry I misread your original post to be greater than 7 days not equal to 7 days. Change the validation rule to exclude the > symbol.

[departure Date]=[arrival Date]+7
Oct 15 '07 #6

P: 19
Thank you for your help :) I tried this before and the same thing happened so I'll try it again and if it does the same thing then I'll ait to see the solution. Again - thanks.
Oct 15 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.