VBA Comparing Short Date formats from text boxes

I am trying to write an IF clause to compare a From Date and a To Date on an Access Form.  If the From Date is greater than the To Date, a message box appears and the user is sent back to the From date to re-enter the data.  The message box will appear when the To Date Date Picker Calendar is clicked.  I am embedding the Code to start ON Change for the DateTo text box.

The two text boxes are formatted as Short Dates and use the Date Picker to enter the data.

Here is my code:

If DateSerial([Forms]![frmStartOrderDate]!DateTo) < DateSerial([Forms]![frmStartOrderDate]!DateFrom) Then
        MsgBox "From Date " & Me.DateFrom & " is GREATER THAN " & Me.DateTo & ".  Please Correct!"
        Me.DateTo.Value = ""
        Me.DateFrom.Value = ""
End If

Currently the Message Box is not appearing when I enter a DateTo less than the DateFrom.

What can I do to correct this?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
It's DateValue:

If DateValue([Forms]![frmStartOrderDate]!DateTo) < DateValue([Forms]![frmStartOrderDate]!DateFrom) Then

GPSPOWAuthor Commented:

I am getting a Data Type Mismatch error:

If DateValue([Forms]![frmStartOrderDate]!DateTo) < DateValue([Forms]![frmStartOrderDate]!DateFrom) Then
            MsgBox "From Date " & Me.[Forms]![frmStartOrderDate]!DateFrom & " is GREATER THAN " & [Forms]![frmStartOrderDate]!DateTo & ".  Please Correct!"
Rey Obrero (Capricorn1)Commented:

If Forms![frmStartOrderDate]!DateTo <    Forms![frmStartOrderDate]!DateFrom Then

MsgBox "From Date " & Forms![frmStartOrderDate]!DateFrom & " is GREATER THAN " & Forms![frmStartOrderDate]!DateTo & ".  Please Correct!"
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

The code does not belong in the Change event.  It belongs in the BeforeUpdate event of the control or the BeforeUpdate event of the Form.  You must be very careful with code that involves more than one control.  If you put it in the BeforeUpdate event of controlB, what's to say that that the companion data has already been entered in controlA.  Or what happens if the validation is only in controlB and the user goes back later and changes controlA.  The validation won't be rerun.  The easiest solution is to put the code in the Form level BeforeUpdate event so that you can assume that all data has been entered and act appropriately.  That means that you can also raise an error if either control is missing and you can't do that  effectively in the individual control events.

The Change event runs after EVERY KEYSTROKE so you won't even have a valid date until the entire date is entered.  The BeforeUpdate event runs when focus leaves the control (but only if the control has been modified) or if something has forced the record to be saved in which case all the control level events are run before the form level events.

If you are running the code in form level events and referring to controls on the active form, use the Me.controlname syntax.  it will be more efficient.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:

I'll try changing the control event.

PS.  As long as you have the textboxes formatted as dates, Access will NOT allow an invalid value to be entered.

If Forms]![frmStartOrderDate]!DateTo  < [Forms]![frmStartOrderDate]!DateFrom Then

If Me.DateTo  < Me.DateFrom Then

You don't need to convert the values to dates.  They already are dates.
Gustav BrockCIOCommented:
You probably have Null values, thus (in the AfterUpdate event):

If IsDate(Me!DateFrom.Value) And IsDate(Me!DateTo.Value) Then
    If DateValue(Me!DateTo.Value) < DateValue(Me!DateFrom.Value) Then
         MsgBox "From Date " & Me!DateFrom.Value & " is GREATER THAN " & Me!DateTo.Value & ".  Please Correct!"
         Me!DateFrom.Value = Null
    End If
End If

Gustav BrockCIOCommented:
> .. Access will NOT allow an invalid value to be entered.

except Null, and as one control is empty while the first is being input, that is much likely the case.

Null is not an invalid date.  it may be an invalid value though.  I did allude to that but it was subtle.

Null values can only be handled reliably in the form level events.  If you never put focus in a control, a control level event won't run.  So with the two date fields, if they are required, you really need the code to be in the Form level BeforeUpdate event since error traps in the date fields themselves won't fire if neither date is entered.

The trade-off regarding whether you use form level or control level events is how close to the data entry to you want the error message and whether or not the field is required.  If a field is required, you must put code in the form's BeforeUpdate event if you want to give the user a nice message.  Otherwise, he'll get the Access message assuming you have defined the field as required at the table level.   I've seen apps with validation code in every control event and none of it gets executed if the user doesn't put focus in any of the controls.  Apparently one programmer recognized that and on one of his forms, he had validation code in the lost focus event of control1 for control1, validation code in the lost focus event of control2 for control1 and 2,  in control3 he validated 3,2,1, etc.  And all of it was bypassable.
GPSPOWAuthor Commented:
I moved the code to next button in the sequence of form options as an OnClick.  It then evaluates the values of the DateFrom and DateTo.
The DateFrom and DateTo both have navigation code to move the cursor along as an OnClick event.

If the DateTo< DateFrom it displays the message and sends the user back to the DateFrom input area to start again.

It works fine now.


It may appear to work but it is poor practice.  The events of a form are not arbitrary.  They are intended for specific uses.  When you deviate from the grand plan, you do so at your own risk.  You also make it more difficult for your successor and even yourself in a year because you did something non-standard.  There is certainly a time and a place for blazing your own path but this wasn't it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.