validation code for dates

[sheena]

hi I have two fields in my table date1 and date2 i need to write a validation
code so that any dates entered in the date2 field are either eqaul to or
within 10 days on the date entered in date 1. if the date exceeds this i want
my validation error message to appear.
does anyone know how i can go about writing the validation code as i have
tried so many different ways and i have had no luck so far.

Thank you
Sheena

 

[Allen Browne]

When comparing 2 fields like that, you need to use record-level validation
rather than field-level. A simple way to do that is to use the Validation
Rule of the table. If you don't wish to do that you could use the
BeforeUpdate event procedure of the form, but this solution requires no
code.

1. Open your table in design view.

2. Open the Properties box.

3. Beside the table's Validation Rule in the Properties box, enter an
expression like this:
([Date2] Is Null) OR (Date2] - [Date1] Between -10 And 10)

4. Beside the table's Validation Text, enter your validation message.

Please don't use the Validation Rule in the lower pane of table design:
that's the rule for a field, and is not suitable for comparing fields.

More info:
http://allenbrowne.com/ValidationRule.html

 

[Linq Adams via AccessMonster.com]

You need to define "within 10 days on the date entered in date 1." Do you
mean within date1 + 10 days or date1 +/- 10 days?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[sheena]

yes it has to be within 10 days on date 1 going forwards not backwards

 

[sheena]

hi i have tried this and it came up with an error message saying check
constraints i entered incorrect data to test this as well as correct data but
the same message appeared for both

When comparing 2 fields like that, you need to use record-level validation
rather than field-level. A simple way to do that is to use the Validation
Rule of the table. If you don't wish to do that you could use the
BeforeUpdate event procedure of the form, but this solution requires no
code.

1. Open your table in design view.

2. Open the Properties box.

3. Beside the table's Validation Rule in the Properties box, enter an
expression like this:
([Date2] Is Null) OR (Date2] - [Date1] Between -10 And 10)

4. Beside the table's Validation Text, enter your validation message.

Please don't use the Validation Rule in the lower pane of table design:
that's the rule for a field, and is not suitable for comparing fields.

More info:
http://allenbrowne.com/ValidationRule.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hi I have two fields in my table date1 and date2 i need to write a
validation
code so that any dates entered in the date2 field are either eqaul to or
within 10 days on the date entered in date 1. if the date exceeds this i
want
my validation error message to appear.
does anyone know how i can go about writing the validation code as i have
tried so many different ways and i have had no luck so far.

Thank you
Sheena

 

[Allen Browne]

Missed a square bracket:
([Date2] Is Null) Or ([Date2]-[Date1] Between -10 And 10)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hi i have tried this and it came up with an error message saying check
constraints i entered incorrect data to test this as well as correct data
but
the same message appeared for both

When comparing 2 fields like that, you need to use record-level
validation
rather than field-level. A simple way to do that is to use the Validation
Rule of the table. If you don't wish to do that you could use the
BeforeUpdate event procedure of the form, but this solution requires no
code.

1. Open your table in design view.

2. Open the Properties box.

3. Beside the table's Validation Rule in the Properties box, enter an
expression like this:
([Date2] Is Null) OR (Date2] - [Date1] Between -10 And 10)

4. Beside the table's Validation Text, enter your validation message.

Please don't use the Validation Rule in the lower pane of table design:
that's the rule for a field, and is not suitable for comparing fields.

More info:
http://allenbrowne.com/ValidationRule.html

hi I have two fields in my table date1 and date2 i need to write a
validation
code so that any dates entered in the date2 field are either eqaul to
or
within 10 days on the date entered in date 1. if the date exceeds this
i
want
my validation error message to appear.
does anyone know how i can go about writing the validation code as i
have
tried so many different ways and i have had no luck so far.

 

[sheena]

thanks that has worked, thank you very much for your help.

Missed a square bracket:
([Date2] Is Null) Or ([Date2]-[Date1] Between -10 And 10)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hi i have tried this and it came up with an error message saying check
constraints i entered incorrect data to test this as well as correct data
but
the same message appeared for both

When comparing 2 fields like that, you need to use record-level
validation
rather than field-level. A simple way to do that is to use the Validation
Rule of the table. If you don't wish to do that you could use the
BeforeUpdate event procedure of the form, but this solution requires no
code.

1. Open your table in design view.

2. Open the Properties box.

3. Beside the table's Validation Rule in the Properties box, enter an
expression like this:
([Date2] Is Null) OR (Date2] - [Date1] Between -10 And 10)

4. Beside the table's Validation Text, enter your validation message.

Please don't use the Validation Rule in the lower pane of table design:
that's the rule for a field, and is not suitable for comparing fields.

More info:
http://allenbrowne.com/ValidationRule.html

hi I have two fields in my table date1 and date2 i need to write a
validation
code so that any dates entered in the date2 field are either eqaul to
or
within 10 days on the date entered in date 1. if the date exceeds this
i
want
my validation error message to appear.
does anyone know how i can go about writing the validation code as i
have
tried so many different ways and i have had no luck so far.