How to compare two date fields in an Access Form?

[stress999]

I am having difficulty comparing two different date fields on my access form. Ex. If date two is less than date one... I want the system to display a msg... saying date two must be greater than date one.

[twinnyfo]

Hi stress999,

Assuming you have some controls named similarly as below, you would have VBA behind each of your Date fields in the AfterUpdate Event. Your code would look similar to this:

Option Compare Database
Option Explicit

Private Sub txtDate1_AfterUpdate()
    VerifyDates
End Sub
Private Sub txtDate2_AfterUpdate()
    VerifyDates
End Sub
Private Sub VerifyDates()
    If Not (IsNull(Me.txtDate1) Or IsNull(Me.txtDate2)) Then
        If Me.txtDate2 < Me.txtDate1 Then
            Me.txtWarning = "Date 2 must be after Date 1"
        Else
            Me.txtWarning = ""
        End If
    End If
End Sub

Normally I don't write the whole thing out, but I had a few minutes and this should get you started.

Hope this hepps!

[stress999]

Thanks Twinnyfo - this is syntax I was looking for. Great stuff!

[stress999]

Hi Twinnyfo,
I tried to insert the code above... for some reason it errors out. To test the exact code, I created 2 text boxes with the field names you used in the code.
The code was inserted from the "General" option.

[twinnyfo]

stress999,

First, I don't know what "General" option you are referring to.

Second, at what point of the code is it producing an error? This will help us to trouble shoot.

[stress999]

Twinnyfo,
I placed the code in the "General" option from the the drop down option where are the named fields are stored. It errors out at "Private Sub verifyDates() and "txtWarning .

Where should I placed the code? I have identical named field on the form as you created in the code: "txtDate1 and "txtDate2. I am not sure what is going on...

[twinnyfo]

This code should be in the VBA module for the form that has the two text controls on it.

[stress999]

Twinnyfo,

The error occurs at "Private Sub VerifyDate()" in the code below

Option Compare Database
Option Explicit
Private Sub txtDate1_AfterUpdate()
    VerifyDates
End Sub

Private Sub txtDate2_AfterUpdate()
    VerifyDates
End Sub

Private Sub VerifyDates()
    If Not (IsNull(Me.txtDate1) Or IsNull(Me.txtDate2)) Then
        If Me.txtDate2 < Me.txtDate1 Then
            Me.txtWarning = "Date 2 must be after Date 1"

        Else
            Me.txtWarning = ""
        End If

    End If
End Sub

[twinnyfo]

First, please use Code tags when posting Code.

What is the error that you are receiving? This will tell us how to troubleshoot.

[stress999]

The error occurred at line 10 and 15 in the original code you posted.
Line 15 kicks out compile error: "Method or data member not found" when click text box that represents "textDate2

[twinnyfo]

What are the names of the controls on your form. You must either 1) replace the control names in the code with the names of your controls or 2) rename your controls to match what is in the code.

[stress999]

Twinnyfo, the controls on the form are the same as the code above...txtDate 1 and txtDate2.
I switched the control names. Now the compile error on LINES 14 states: Method or data member not found

[twinnyfo]

I can only imagine that you don't have a text box named txtWarning.....

[stress999]

Thx twinnyfo got it...

[twinnyfo]

Great to hear it! Let us know if you need anything else.