Access Validation Rule Question. Help Greatly Appreciated.

[BrianB830]

Hello all,

I have a quick question regarding an MS Access database I'm creating.
In the entity "ORDER", I have the attributes "Order Date" and
"Delivery Date". I need to create a validation rule here. The
delivery date must come after the order date.

Would anyone be able/willing to help?

Thanks everyone!

[Steve Jorgensen]

On Sun, 02 Oct 2005 02:10:41 -0500, BB*******@adelp hia-dot-net.no-spam.invalid
(BrianB830) wrote:

Hello all,

I have a quick question regarding an MS Access database I'm creating.
In the entity "ORDER", I have the attributes "Order Date" and
"Delivery Date". I need to create a validation rule here. The
delivery date must come after the order date.

Would anyone be able/willing to help?

Thanks everyone!

I'm pretty sure you can't use a database-level validation rule to enforce
that. You'll just have to use a form for input, and validate there. Do the
validation check in the BeforeUpdate handler on the form.

Let's assume you have 2 textboxes on your form bound to the 2 fields, and the
controls are called txtOrderDate and txtDeliveryDate . Let's also assume for
purposes of example that both fields are required and this is enforced with
field settings, so BeforeUpdate for the form cannot be triggered without
filling in both dates first. In this case, your even handler might look
like...

Private Sub Form_BeforeUpda te(Cancel As Integer)

If Me!txtDeliveryD ate < Me!txtOrderDate Then
Cancel = True
Me!txtDeliveryD ate.SetFocus
MsgBox "The Delivery Date must be on or after the " & _
"Order Date."
End If

End Sub

Note - for many reasons, it's best not to include spaces in table or field
names, and avoid using key words as names ("ORDER" is an SQL key word). It is
conventional in Access to use Camel Case with a "tbl" object type prefix for
table names, and Upper Cammel Case (e.g. tblOrder, OrderDate,
OrderDeliveryDa te). In the relational DB server world, lower case with
underscores is more common (e.g. sale_order, order_date, delivery_date).
Either of these styles will make sense in an Access database.

[Trevor Best]

BrianB830 wrote:

Hello all,

I have a quick question regarding an MS Access database I'm creating.
In the entity "ORDER", I have the attributes "Order Date" and
"Delivery Date". I need to create a validation rule here. The
delivery date must come after the order date.

Would anyone be able/willing to help?

Thanks everyone!

Using table validation, not field validation, you'd do something like:

[Delivery Date] >= [Order Date]

BTW, you might want to rethink your naming conventions, as meaningful as
your names are, "Order" and "date" are reserved words in Access/SQL and
can cause you a problem later on.

[Bruce Rusk]

You *could* do this with a Check Constraint (in Access 2000 and later). This
would probably be more efficient but there are some drawbacks:

- it's poorly documented, and can only be created from VBA code, not from
the Access UI

- it doesn't give friendly error messages when the constraint is violated
(but you can name the contraint in a fairly friendly manner)

- Jet supports more flexible constaints than SQL Server, so you have to be
careful if there's a chance you might upgrade in the future.

If you're interested in this approach, take a look at:

http://support.microsoft.com/Default.aspx?kbid=201888

"BrianB830" <BB*******@adel phia-dot-net.no-spam.invalid> wrote in message
news:6b******** ************@gi ganews.com...

Hello all,

I have a quick question regarding an MS Access database I'm creating.
In the entity "ORDER", I have the attributes "Order Date" and
"Delivery Date". I need to create a validation rule here. The
delivery date must come after the order date.

Would anyone be able/willing to help?

Thanks everyone!