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.

 

[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.

 

[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.