Validation Rule Question

[peashoe]

How do I create a validation rule using data already in the table. For
example: I have a "Check-In Date"and a "Arrival Date". The User will
enter the "Check In Date" first - but I need a validation rule that
says:

If Arrival Date > Check-In Date
msgbox ("Need a new date")

I tried putting < [Check In Date] in the Arrival Date validation rule,
but it will not work.

Is this even possible?

Thanks in advance for your help
Lisa

 

[peashoe]

I also tried creating an event

Private Sub Attendees_Enter()
If [Check In] > [Arrival Date/Time] Then
MsgBox ("Date is wrong")
End If
End Sub

and it doesn't work??

 

[Rick Brandt]

How do I create a validation rule using data already in the table. For
example: I have a "Check-In Date"and a "Arrival Date". The User will
enter the "Check In Date" first - but I need a validation rule that
says:

If Arrival Date > Check-In Date
msgbox ("Need a new date")

I tried putting < [Check In Date] in the Arrival Date validation rule,
but it will not work.

Is this even possible?

Thanks in advance for your help
Lisa

You need a "table validation rule" rather than a "field validation rule" if
you want to do this in the design of the table. Field validation rules are
not allowed to refer to other fields.

You have to look at the property sheet of the table while in design view to
see where the table validation rule is entered. Off the top of my head I
would think something like...

[Arrival Date] <= [Check-In Date]

You could use the BeforeUpdate event of the form used for data entry as
well, but doing it at the table design level is better if you can get it to
work.

 

[peashoe]

Rick,
That helped! I added:

Private Sub Arrival_Date_LostFocus()
If ([Arrival Date] > [Check In]) Then
MsgBox ("The Arrival Date is greater then the Check In Date")
End If
End Sub

in the textbox event for Arrival Date on the form and it works!

Thank you SO much!
Lisa

 

[Rick Brandt]

Rick,
That helped! I added:

Private Sub Arrival_Date_LostFocus()
If ([Arrival Date] > [Check In]) Then
MsgBox ("The Arrival Date is greater then the Check In Date")
End If
End Sub

in the textbox event for Arrival Date on the form and it works!

Thank you SO much!
Lisa

In a form it would be better to use the BeforeUpdate event of the form as well.
If your user fills in the Check In field after the Arrival Date field (and they
sometimes will) then your current code will not catch it.

 

[Jamie Collins]

(e-mail address removed) wrote:
You need a "table validation rule" rather than a "field validation rule" if
you want to do this in the design of the table. Field validation rules are
not allowed to refer to other fields.

Actually, the OP requires a record-level (row-level) validation rule.

A record-level Validation Rule can reference fields (columns) in the
same record. What would one call a Validation Rule rule that could
reference other rows in the table (or other tables)? A table-level
Validation Rule, of
course <g>. There is no such animal but Jet 4.0/Access 2007 engine has
CHECK constraints, which are truly table-level.

Jamie.

--

 

[Rick Brandt]

Actually, the OP requires a record-level (row-level) validation rule.

A record-level Validation Rule can reference fields (columns) in the
same record. What would one call a Validation Rule rule that could
reference other rows in the table (or other tables)? A table-level
Validation Rule, of
course <g>. There is no such animal but Jet 4.0/Access 2007 engine has
CHECK constraints, which are truly table-level.

Jamie.

Yeah, whatever. To create this in Access you are viewing a dialog named
"Table Properties" and the property is named "Validation Rule". To me, that
makes it a table validation rule.

 

[Jamie Collins]

Yeah, whatever.

To create this in Access you are viewing a dialog named
"Table Properties" and the property is named "Validation Rule". To me, that
makes it a table validation rule.

Then you are mistaken, the fault of a poor user interface I'd say

A bit of reading around the subject should the answer e.g.

About restricting or validating data (MDB)
http://office.microsoft.com/en-us/assistance/HP052623481033.aspx

"You can define two kinds of validation rules: field validation rules
and record validation rules."

I ask you again to consider, if the Access team ever get around to
exposing Jet 4.0 table-level CHECK constraints as validation rules in
the UI, how would you refer to one if you have already attached the
moniker 'table validation rule' to some other object type?

Jamie.

--

 

[Rick Brandt]

Then you are mistaken, the fault of a poor user interface I'd say

Then I guess we should stop referring to BeforeUpdate, BeforeInsert,
AfterUpdate, AfterInsert, BeforeDelete, etc., as "form events" as well. After
all it is the record which is being updated, inserted, and deleted. The fact
that the property sheet where these are found is labeled "Form" is just another
bad interface example.

Tell me, what are the first two words of the DDL to add a "check constraint"?
ALTER "what". While they are applied at the time that rows are manipulated,
there is no doubt that they are part of the Table's definition. From MSDN...

**********************************************
Check Constraints
A check constraint specifies the data values or formats that are acceptable in
one or more columns in a table. For example, you can require the zip column of
the authors table to allow only five-digit numeric entries.

You can define many check constraints for a table. You use the Tables property
pages to create, modify, or delete each check constraint.
**********************************************

Hmm, I see the word "table" and I see the word "column", but no mention of the
word "record". I will alert MS to this documentation error.

 

[Jamie Collins]

Tell me, what are the first two words of the DDL to add a "check constraint"?
ALTER "what".

TABLE. Yes, unlike Validation Rules, Jet CHECK constraints are always
defined at the table level. But wasn't that *my* point?

From MSDN...

**********************************************
Check Constraints
A check constraint specifies the data values or formats that are acceptable in
one or more columns in a table. For example, you can require the zip column of
the authors table to allow only five-digit numeric entries.

You can define many check constraints for a table. You use the Tables property
pages to create, modify, or delete each check constraint.
**********************************************

Come now, Rick. That documentation doesn't related to Jet or the Access
UI, does it?

Hmm, I see the word "table" and I see the word "column", but no mention of the
word "record". I will alert MS to this documentation error.

I think you should 'hold fire' on that one. If you want to alter a
record (row), rather than look at ALTER in SQL DDL, I suggest you look
for the keyword UPDATE in SQL DML. (I tried to warn you that using
'table' and 'row' interchangeably might lead to misunderstandings <g>.)

This would have made for a better quote:

**********************************************
Jet OLEDB:Table Validation Rule

A String value (read/write) that specifies the expression to be
evaluated on a table in order to validate the values of a record before
saving the record's changes.
**********************************************

Could you use your apparent influence to get *this* fixed, please <g>?

Jamie.

--