Compare Dates [resolved]

[demotivater]

What's the best way to check if one date is greater than another? I have two textboxes with dates in them, want to make sure one of the dates is not greater than the other. The below works sometimes, but I just ran into a case where it isn't working.

If txtBidDate.text <> "" Then
                        If txtReviewDate.text > txtBidDate.text Then
                            MsgBox "The review date cannot be past the bid date", vbCritical, "Error"
                            txtReviewDate.text = ""
                            cmdReviewDate.SetFocus
                            Exit Sub
                        End If
                    End If

reviewdate = 6/10/04
biddate = 6/9/04

Is it because I need to format them both to = mm/dd/yyyy so 6/9/04 would become 6/09/04, or is there a better way?

Thanks in advance

[brucevde]

Since you are comparing string variables and not dates, make sure the strings are the exact same length and format.

by the way mm/dd/yyyy is 06/04/2004

Another option is to convert the text values to date variables and use the DateDiff function.

[Pasvorto]

I would do this:

if cdate(txtdate1.text) > cdate(txtdate2.text) then...

[Pasvorto]

txtdate1.text = format(cdate(txtdate1.text),"mm/dd/yyy")

[ccoder]

One more thing. The dates should always be in year-month-day format as in "2004/06/04". Otherwise, the following would test true.

If "06/04/2000" > "06/03/2004" Then ....

[techgnome]

Originally posted by ccoder
One more thing. The dates should always be in year-month-day format as in "2004/06/04". Otherwise, the following would test true.

If "06/04/2000" > "06/03/2004" Then ....

Only because they are being treated as strings. If converted to Date data type, then the proper comparison will take place and return the right result.

TG

[Merri]

You might want to use DateSerial to get the dates correct. Of course you have to parse the date strings by yourself, but atleast that way they go right no matter what.

Dim Date1() As String, Date2() As String
Dim rDate As Date, bDate As Date
 
'parse data
Date1 = Split(txtReviewDate.Text, "/")
Date2 = Split(txtBidDate.Text, "/")
'check for incorrect number of elements
If UBound(Date1) <> 2 Then Exit Sub
If UBound(Date2) <> 2 Then Exit Sub
'make actual dates (year, month, day)
rDate = DateSerial(Date1(2), Date1(1), Date1(0))
bDate = DateSerial(Date2(2), Date2(1), Date2(0))
If rDate > bDate Then 'the date is bigger
    'do stuff
Else 'it is not
    'do stuff
End If

[demotivater]

Thanks for the suggestions guys.

[ccoder]

Originally posted by techgnome
Only because they are being treated as strings. If converted to Date data type, then the proper comparison will take place and return the right result.

TG

Agreed! I had actually responded to the post by brucevde, but failed to quote the post.

FWIW, in all of my years of programming, I have always worked with dates as a "string" of numbers only. Beginning with COBOL and Fortran back in the early 70's and later with PL/1 and C (and a few obscure language variations like MOBOL and SCOBOL). Dates were always stored in [yy]yymmdd format. Back then memory & storage costs were tremendous compared to today. It was too costly to store the "/". The big Y2K flap was a result of storing dates with just the 2 low order digits - another cost saving measure. No one ever dreamed that it would come back to bite them.