How to compare dates in two form fields

[wardw]

In Access 2000, I'm trying to validate two fields containing dates. The date 1PublicationDate field must be later than the date in the DeathDate field, so I'm using an OnChange event on the 1PublicationDate field.

When I make a change in that field, Visual Basic shows this error: "Run-time error '2465': Microsoft Access can't find the field '|' referred to in your expression."



I'm using this code:

Private Sub FirstPublicationDate_Change()

If DateDiff("d", [Me.DeathDate], [Me.1PublicationDate]) < 0 Then GoTo Err_FirstPublicationDate_Change

Exit_FirstPublicationDate_Change:
    Exit Sub

Err_FirstPublicationDate_Change:
    MsgBox "First Publication Date must be same as or later than Death Date."
End Sub

The bold portion is what VB highlights in debug mode.

Where's the error in my syntax, or is there a simpler way to validate the dates?

[June7]

Try the AfterUpdate event instead of Change

And:

If Me.1PublicationDate < Me.Deathdate Then

[Missinglinq]

June7's advice about using the AfterUpdate event, instead of the OnChange event, is dead on, because the OnChange fires with each character that is entered, not after all data is entered, but it won't solve your problem!

According to the posted code. the Control is named

Private Sub FirstPublicationDate_Change()

But you code refers to a Control named

1PublicationDate

which, I suspect, is the field that Access cannot find!

Linq ;0)>

[wardw]

Thanks for your help, June7 and Linq. I changed to AfterUpdate and fixed the name discrepancy, and now all is well.

[Missinglinq]

Glad we could help!

Linq ;0)>