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...txtDate1 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.

[stress999]

Thx a lot Twinnyfo
I would like to force the user to enter the correct dates. Not just a warning message when the dates are incorrect.

[twinnyfo]

There are lots of methods, preferences and options for doing that. It may be more appropriate to start a new thread for that discussion.

One option to begin with is to force the user to use the Date Picker when they select a date. This will work, but can be cumbersome if you have to enter a date from 12 years ago.

I always prefer to use an input mask. This will force the user to enter a Date in Date Format i.e., if you have the input mask set properly, and you want the user to enter the date in the format of "dd-mmm-yyyy" those are the only characters they can enter.

Once they enter the date (AfterUpdate Event), you compare that date to your required parameters--whatever they may be--and if the date meets those criteria, move along. If not, then force the user to try again.

Lots of ways to do this.....