Validating dates in tables

[chrisyb322]

Hi there, just a quick question really.
I have got a start date and an end date field in a table both in Date/Time data type. Can i validate the end date so that it cannot be longer than 5 days after the start date, that has been entered. Or is this impossible to do in a table?

Thanks for any help

 

[boblarson]

In the validation in the table you can use

=DateDiff("d",[YourFieldNameHere],Date())<=5

EDIT - SCRATCH THAT - as I don't believe you can validate off of another field in the table.

But you can do it in the form for inputting the data.

 

[chrisyb322]

How would i do it in a form?
Thanks


Edit: OK i do know how to do it but it comes up with
Control Cannot be edited; it's bound to the expression '=DateDiff("d",[Date Booked From],Date())<=5'

Edit: That was my own fault i was putting it in the control source and not the validation rule. BUt now i have put it in the validation rule, the validation doesnt work, i am stil able to input dates more than 5 days later

Thanks for the support

 

[RuralGuy]

Bob,
I think you were on the right track with a query, except you needed to use the two date fields rather than Date(). The question is: What is to be done if the answer is > 5 days? OP???

 

[RuralGuy]

It goes without saying that it is much better to validate the data as it is entered in a form rather than wait until later and try and deal with it.

 

[AccessJunkie]

Sure, you can easily do this. What you need to do is to define a table level validation, not a field level validation. Field level validations cannot reference other fields, but a table validation rule allows you tighter control because you can check across many fields.

You need to open the table's property sheet to get to this setting. How you get there depends upon what version you're using, but essentially you open the table in design view and open the property sheet. You should see the property sheet say "Table Properties" across the top.

Next, look for the property called Validation Rule and enter something like this:
[EndDate]<=[StartDate]+5

On the property called Validation Text, enter something like this:
The End Date cannot be greater than five days from the start date.

I highly recommend you enter a validation text, otherwise your users will see a cryptic error message if the validation fails. This validation rule will be checked before any record is saved. Save the rule changes now to the table and Access will prompt you that it needs to run the new validation rule against existing data. You'll need to correct this data.

By doing this validation rule at the table level, you've created a solid lock to prevent users from entering "bad" data no matter how they do it - directly in table datasheet, query, form, VBA code, etc. I still think it's a good idea to set up some validation at the form level as the others have stated (because you can have much friendlier messages, instant feedback, etc.), but creating this rule at the data level provides a "fool-proof" method.

--------------------
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

 

[boblarson]

Wow! Something else I didn't know and have never used. Cool - Thanks for that Jeff!

 

[boblarson]

Bob,
I think you were on the right track with a query, except you needed to use the two date fields rather than Date(). The question is: What is to be done if the answer is > 5 days? OP???

And yet another brain fart - sheesh, I think I should just go away from the board for a few days.

 

[AccessJunkie]

Wow! Something else I didn't know and have never used. Cool - Thanks for that Jeff!

No problem Bob, glad to help.


--------------------
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

 

[chrisyb322]

Sure, you can easily do this. What you need to do is to define a table level validation, not a field level validation. Field level validations cannot reference other fields, but a table validation rule allows you tighter control because you can check across many fields.

You need to open the table's property sheet to get to this setting. How you get there depends upon what version you're using, but essentially you open the table in design view and open the property sheet. You should see the property sheet say "Table Properties" across the top.

Next, look for the property called Validation Rule and enter something like this:
[EndDate]<=[StartDate]+5

On the property called Validation Text, enter something like this:
The End Date cannot be greater than five days from the start date.

I highly recommend you enter a validation text, otherwise your users will see a cryptic error message if the validation fails. This validation rule will be checked before any record is saved. Save the rule changes now to the table and Access will prompt you that it needs to run the new validation rule against existing data. You'll need to correct this data.

By doing this validation rule at the table level, you've created a solid lock to prevent users from entering "bad" data no matter how they do it - directly in table datasheet, query, form, VBA code, etc. I still think it's a good idea to set up some validation at the form level as the others have stated (because you can have much friendlier messages, instant feedback, etc.), but creating this rule at the data level provides a "fool-proof" method.

--------------------
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

Genius, cheers m8 that worked perfect, these are great forums

 

[AccessJunkie]

You're welcome, glad to help.
Good luck with your projects.

--------------------
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------