Date Validation

[Guest]

I'm trying to restrict entries into a table so that the date something was
sent can't be before a request for it was put in. In the Validation Rule in a
table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater than
those requested. Any help would be greatly appreciated.

Steve

 

[Douglas J. Steele]

I don't understand the problem.

You're saying in your post that "the dates sent are all greater than those
requested", which is exactly what the Validation Rule you posted said they
should be.

 

[Guest]

Thanks for the reply. So, I've checked the underlying data, and there isn't
anything funny there. However, when I save the table, I get a message saying
that the expression is typed incorrectly or is too complex to be evaluated.
As it's a pretty simple equation, I reckon it's got something to do with the
expression. If the expression is correct, I'm completely stumped as to what
else it might be.

I don't understand the problem.

You're saying in your post that "the dates sent are all greater than those
requested", which is exactly what the Validation Rule you posted said they
should be.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I'm trying to restrict entries into a table so that the date something was
sent can't be before a request for it was put in. In the Validation Rule in a
table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater than
those requested. Any help would be greatly appreciated.

Steve

 

[Guest]

Also, I've taken the speech marks off to see what happens. When I test the
validation, a message appears saying "Invalid SQL Syntax - cannot use
multiple columns in a column-level CHECK constraint".

I don't understand the problem.

You're saying in your post that "the dates sent are all greater than those
requested", which is exactly what the Validation Rule you posted said they
should be.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I'm trying to restrict entries into a table so that the date something was
sent can't be before a request for it was put in. In the Validation Rule in a
table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater than
those requested. Any help would be greatly appreciated.

Steve

 

[Todd Shillam]

Steve,

If you enter a validation rule and there are existing records, the table
data will not change--did you think it would? The validation rule simply
prevents future data entry from violating the rule. Are you trying to
'switch' the two columns and their places in the table?

Todd

 

[Guest]

Hi Todd. I'm not trying to switch the columns, plus I knew that the records
wouldn't change after implementing a validation rule. I've checked this
validation by putting in the connected form 25/04/05 as the DateRequest and
25/06/06 as the date sent (i.e. no validation message should come up). A
message appears saying "Date Sent should be after Date Request". I've also
typed in 25/06/04 as the Date Sent to see if I've mixed something up. That
comes up with the same message (As it should do).

Steve,

If you enter a validation rule and there are existing records, the table
data will not change--did you think it would? The validation rule simply
prevents future data entry from violating the rule. Are you trying to
'switch' the two columns and their places in the table?

Todd

I'm trying to restrict entries into a table so that the date
something was sent can't be before a request for it was put in. In
the Validation Rule in a table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater
than those requested. Any help would be greatly appreciated.

Steve

 

[Brendan Reynolds]

Ah, it's a field-level validation rule. A field-level validation rule can't
refer to other fields, you need a table-level validation rule to do that. To
create a table-level validation rule, open the table in design view and
then, if the Properties window is not visible, choose Properties from the
View menu. The table-level Validation Rule property is the third property
down in the list.

And yes, leave out the quotes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Also, I've taken the speech marks off to see what happens. When I test the
validation, a message appears saying "Invalid SQL Syntax - cannot use
multiple columns in a column-level CHECK constraint".

I don't understand the problem.

You're saying in your post that "the dates sent are all greater than
those
requested", which is exactly what the Validation Rule you posted said
they
should be.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I'm trying to restrict entries into a table so that the date something
was
sent can't be before a request for it was put in. In the Validation
Rule in a
table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater
than
those requested. Any help would be greatly appreciated.

Steve

 

[Guest]

Thank you Brendan, it worked a treat.

Ah, it's a field-level validation rule. A field-level validation rule can't
refer to other fields, you need a table-level validation rule to do that. To
create a table-level validation rule, open the table in design view and
then, if the Properties window is not visible, choose Properties from the
View menu. The table-level Validation Rule property is the third property
down in the list.

And yes, leave out the quotes.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Also, I've taken the speech marks off to see what happens. When I test the
validation, a message appears saying "Invalid SQL Syntax - cannot use
multiple columns in a column-level CHECK constraint".

I don't understand the problem.

You're saying in your post that "the dates sent are all greater than
those
requested", which is exactly what the Validation Rule you posted said
they
should be.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I'm trying to restrict entries into a table so that the date something
was
sent can't be before a request for it was put in. In the Validation
Rule
in a
table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater
than
those requested. Any help would be greatly appreciated.

Steve

 

[Guest]

What if you want a date entered to be greater than a date on a different
table? I have an employee hire date, and a product request date. Obviously,
the product request cannot be before the employee hire. These two data are
on different tables. Thanks.

Rob

Hi Todd. I'm not trying to switch the columns, plus I knew that the records
wouldn't change after implementing a validation rule. I've checked this
validation by putting in the connected form 25/04/05 as the DateRequest and
25/06/06 as the date sent (i.e. no validation message should come up). A
message appears saying "Date Sent should be after Date Request". I've also
typed in 25/06/04 as the Date Sent to see if I've mixed something up. That
comes up with the same message (As it should do).

Steve,

If you enter a validation rule and there are existing records, the table
data will not change--did you think it would? The validation rule simply
prevents future data entry from violating the rule. Are you trying to
'switch' the two columns and their places in the table?

Todd

I'm trying to restrict entries into a table so that the date
something was sent can't be before a request for it was put in. In
the Validation Rule in a table, I've typed in:

"[DateSent]>=[DateRequest]"

I've checked the relevant dates, and the dates sent are all greater
than those requested. Any help would be greatly appreciated.

Steve