DATES

[Guest]

I have two date fields.. One is <dateapptset> and the other is <dateofappt>..
how can I make sure that <dateapptset> is always an earlier date than
<dateofappt>?

Thanks

 

[Dirk Goldgar]

I have two date fields.. One is <dateapptset> and the other is
<dateofappt>.. how can I make sure that <dateapptset> is always an
earlier date than <dateofappt>?

You can enforce this at the table level with a table validation rule,
like this:

[dateapptset]<[dateofappt] Or [dateapptset] Is Null Or [dateofappt]
Is Null

You'd also want to specify an appropriate message in the
ValidationRuleText property.

HOWEVER, the table-level validation should serve only as a backup to the
validation you do in your form, because (a) user entry should only be
made via a form, and (b) you have more control when validating with a
form. So the form where this appointment information is entered should
have a BeforeUpdate event procedure, and that procedure should check
that all the required fields have been entered, and that they are valid
with respect to each other.

Assuming you require both fields to be entered (or defaulted), such an
event procedure might look like this:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me!dateapptset
If IsNull(.Value) Then
MsgBox "Required field not entered.", _
vbExclamation, "Unable to Save"
.SetFocus
Cancel = True
Exit Sub
End If
End With

With Me!dateofappt
If IsNull(.Value) Then
MsgBox "Required field not entered.", _
vbExclamation, "Unable to Save"
.SetFocus
Cancel = True
Exit Sub
End If
End With

If Me!dateapptset >= Me!dateofappt Then
MsgBox "Appointment date must be in the future.", _
vbExclamation, "Unable to Save"
Me!dateofappt.SetFocus
Cancel = True
End If

End Sub
'----- end of example code -----

The above code could certainly be improved, but it should give you an
idea.

 

[Guest]

In your data input form put code along these lines in the form's BeforeUpdate
event procedure:

Const conMESSAGE = "Your message text goes here.""

If Me.dateapptset >= Me.dateofappt Then
MsgBox conMESSAGE, vbExclamation, "Invalid Data"
Cancel = True
End If

Ken Sheridan
Stafford, England

 

[Jamie Collins]

You can enforce this at the table level with a tablevalidationrule,

like this:

[dateapptset]<[dateofappt] Or [dateapptset] Is Null Or [dateofappt]
Is Null

The tests for null are redundant i.e. the validation rule can simply
be:

[dateapptset]<[dateofappt]

This is due to the nature of the null value in SQL DDL (different from
SQL DML where null 'propagates'): a null value cannot be known to fail
a validation rule therefore it is given the 'benefit of the doubt' and
allowed to pass. This implicit behaviour is by design and for pragmatic
reasons i.e. otherwise *every* mention of a nullable column in a
validation rule would have to be explicitly tested for null.

Jamie.

--