By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,839 Members | 2,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,839 IT Pros & Developers. It's quick & easy.

Access Validation Rule Question. Help Greatly Appreciated.

P: n/a
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!

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Sun, 02 Oct 2005 02:10:41 -0500, BB*******@adelphia-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_BeforeUpdate(Cancel As Integer)

If Me!txtDeliveryDate < Me!txtOrderDate Then
Cancel = True
Me!txtDeliveryDate.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,
OrderDeliveryDate). 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.
Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

P: n/a
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*******@adelphia-dot-net.no-spam.invalid> wrote in message
news:6b********************@giganews.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!

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.