Compare Dates in VBA

Question

I have two textboxes with two differents dates, orderDate and recievedDate. The recieved date needs to be entered manually into the form and I want to include the validation that the recievedData should happen after the orderDate, I have tried:

If txtRecievedDate.Text < txtOrderDate.Text Then

   MsgBox "Incorrect Date, item can't be recieved before order"

else    

  MsgBox "correct date"

End If

This doesn't work as for example RecievedDate value is "19/11/2013" and OrderDate value is "20/10/2013" although this would be a correct date this statement only compares the "19" and "20" therefore marking it as incorrect.

Is there a way of comparing two dates within textboxes? For this im using VBA

Thanks

Answer 1

Here's how you can fix your code. Of course it's not the best way to do date calculations, and you'll also have to validate that there is text in the 2 text boxes, and maybe even use CDate() to parse the text to date values, but this will work for your current situation.

If DateDiff("d", txtOrderDate.Text, txtRecievedDate.Text) < 0 Then

   MsgBox "Incorrect Date, item can't be recieved before order"

else    

  MsgBox "correct date"

End If

Answer 2

Apart from the excellent solution of Siddharth Rout at Formatting MM/DD/YYYY dates in textbox in VBA, I'll suggest the following procedure. UserForm is a natural object in Excel, but we shall make reference to an OCX object to use it in Access, so not directly available.

In Access with form object, one can do this:

Sub sof20270928CompareDates()
  If CDate(txtRecievedDate.Value) < CDate(txtOrderDate.Value) Then
    MsgBox "Incorrect Date, item can't be recieved before order"
  Else
    MsgBox "correct date"
  End If
End Sub

Instead of comparing text strings, we compare now dates. CDate() converts local date time strings into date type. An American will type the date in mm/dd/yyyy format, like 11/19/2013, in France one types dd/mm/yyyy, like 19/11/2013. CDate() will take care of this as Access considers the computer's locale.

Moreover, txtRecievedDate.Value is preferealbe to txtRecievedDate.Text, the latter requires focus, ie, the control must be active to get the .text property.

Answer 3

try comparing them in date formats instead of in .text format

for example

Sub datee()
Dim Orderdate As Date

Dim Recievedate As Date

Orderdate = Worksheets("Sheet1").Range("A1").Value
Recievedate = InputBox("Enter date recieved")

If Recievedate < Orderdate Then
MsgBox "error"
Exit Sub
Else
Worksheets("sheet1").Range("a3").Value = Recievedate
End If


End Sub

Answer 4

If you want to compare just dates, and not times, then the function DATEVALUE will remove the time from a date and time value for easy comparison.