Validate a Date

[rm]

How do I get rid of the less then friendly "The value you entered
isn't valid for this field" message?

I have a table with a date field. In the table design for the field

Validation Text = "Please Enter a Valid Date"
Validation Rule = "Is Null Or IsDate([dob])=True"

The control on the form has the same Validation Text and Rule.

When the user enters an invalid date I still get the "The value you
entered.." message. How do I make the Validation Text display
instead.

I have tried both the control Before Update and the Form Before
Update. No luck w/ either one of those. Even more, what is the purpose
of having a Validation Rule and Validation Text if they cannot be used
to validate data?

 

[Ken Sheridan]

I don't know that there's any way you can do this with a bound control on a
form, but provided the form is in single form view you could use an unbound
control and in its BeforeUpdate event procedure put:

Const MESSAGE = "Please enter a valid date."
Dim ctrl As Control

Set ctrl = Me.ActiveControl

If IsNull(ctrl) Or Not IsDate(ctrl) Then
MsgBox MESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
Else
' assign value to date field
Me.YourDateField = CDate(ctrl)
End If

In the form's Current event procedure put:

' assign value of date field to unbound control
Me.YourUnboundControl = Me.YourDateField

It wouldn't work in continuous form view, however, as each row would show
the date from the currently selected row.

As regards validation rules they can validate whether legitimate values of
the data type in question fit a defined rule, e.g. if the date is within a
certain range, but the system validation of whether a value is a legitimate
one per se for the data type in question takes precedence.

Ken Sheridan
Stafford, England