-
Oct 21st, 2020, 02:33 PM
#1
Thread Starter
Fanatic Member
Check if Date is greater than todays date
Hello:
VBA Excel (very old school insanity).
I have this very simple line:
Code:
If (Format(rs.Fields("ShipDate"), "yyyy-mm-dd") > Format(Now(), "yyyy-mm-dd")) = True Then
On these dates:
Code:
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>2020-08-20 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2021-02-22 > 2020-10-21
===>2020-09-28 > 2020-10-21
===>2020-09-28 > 2020-10-21
===>2020-11-16 > 2020-10-21
===>2020-11-16 > 2020-10-21
===>2020-11-16 > 2020-10-21
===>2020-11-16 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2021-03-22 > 2020-10-21
===>2020-09-11 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2021-01-25 > 2020-10-21
===>2020-11-09 > 2020-10-21
===>1979-01-01 > 2020-10-21
===>2020-11-06 > 2020-10-21
Some of the dates are in fact greater than today's date, but I get nothing. If I change it to less than, I get everything, and some of those dates are greater than todays date.
Thanks!
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 21st, 2020, 02:40 PM
#2
Re: Check if Date is greater than todays date
what happens when you change it to this:
Code:
If rs.Fields("ShipDate") > Now() Then
-tg
-
Oct 21st, 2020, 02:50 PM
#3
Re: Check if Date is greater than todays date
Code:
If CStr((Format(rs.Fields("ShipDate"), "yyyy-mm-dd")) > CStr(Format(Now(), "yyyy-mm-dd"))) = True Then
Or
Code:
Option Explicit
Sub CompareDateFields()
Dim strTemp1 As String, strTemp2 As String
Dim intTemp1 As Integer
For intTemp1 = 1 To 74
strTemp1 = CStr(Sheet1.Cells(intTemp1, 1))
strTemp2 = CStr(Sheet1.Cells(intTemp1, 2))
' Stop
If strTemp1 > strTemp2 Then
Sheet1.Cells(intTemp1, 3) = "True"
Else
Sheet1.Cells(intTemp1, 3) = "False"
End If
Next intTemp1
End Sub
The above macro works on a sheet with your dates in rows 1-72, columns 1-2
-
Oct 21st, 2020, 02:55 PM
#4
Thread Starter
Fanatic Member
Re: Check if Date is greater than todays date
That's about where I started.
It isn't reading the dates because it puts everything in...
To elaborate further...
Code:
Dim i As Integer
i = 3
Do While Not rs.EOF
Debug.Print ("===>" & rs.Fields("ShipDate") & " > " & Format(Now(), "yyyy-mm-dd"))
If rs.Fields("ShipDate") > Now() Then
ThisWorkbook.ActiveSheet.Cells(i, 1).Value = (rs.Fields("ShipTo"))
ThisWorkbook.ActiveSheet.Cells(i, 2).Value = (rs.Fields("Product"))
ThisWorkbook.ActiveSheet.Cells(i, 3).Value = (rs.Fields("JobNo"))
ThisWorkbook.ActiveSheet.Cells(i, 4).Value = (rs.Fields("Detail"))
ThisWorkbook.ActiveSheet.Cells(i, 5).Value = (rs.Fields("Feet"))
ThisWorkbook.ActiveSheet.Cells(i, 7).Value = (rs.Fields("Designer"))
ThisWorkbook.ActiveSheet.Cells(i, 8).Value = (rs.Fields("EstHrs"))
If (rs.Fields("Started")) <> #1/1/1979# Then
ThisWorkbook.ActiveSheet.Cells(i, 9).Value = (rs.Fields("Started"))
Else
ThisWorkbook.ActiveSheet.Cells(i, 9).Value = ""
End If
If (rs.Fields("Completed")) <> #1/1/1979# Then
ThisWorkbook.ActiveSheet.Cells(i, 10).Value = (rs.Fields("Completed"))
Else
ThisWorkbook.ActiveSheet.Cells(i, 10).Value = ""
End If
If (rs.Fields("ShipDate")) <> #1/1/1979# Then
ThisWorkbook.ActiveSheet.Cells(i, 13).Value = (rs.Fields("ShipDate"))
Else
ThisWorkbook.ActiveSheet.Cells(i, 13).Value = ""
End If
End If
rs.MoveNext
i = i + 1
Loop
- A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!
-
Oct 21st, 2020, 04:39 PM
#5
Re: Check if Date is greater than todays date
Regarding your code line in post #1, who knows what data types the 2 expressions will be. If you actually tried the code I posted, your issue would probably be solved. That codes compares a String against another String, so we know the data types, and it works.
-
Oct 22nd, 2020, 12:39 AM
#6
Re: Check if Date is greater than todays date
You really should not try to compare dates as strings. Compare them as dates and you will get the desired results.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|