How to detect one date field value is later/greater than the other date field?

I have one textbox(displays count result), one button(performs the counting), and two DateTimePickers(for date period from and to) in a form. I am using MS Access database for my program with two date fields(date1 and date2). What I want is to count how many records dated with(dates given from datetimepickerfrom and datetimepickerto). The counting is with condition like: If date2 is greater or later than date1 then the counting will base on date2, else on date1. Any suggestion or modification in my code is highly appreciated.


This is my code but it results error:"Item cannot be found in the collection corresponding to the requested name or ordinal."
countrec = New ADODB.Recordset
With countrec

If .Fields("Date2").Value > .Fields("Date1").Value Then
.Open("select count(*) as count2 from Docstable where Date2 >=#" & DateTimePickerfrom.Value.Date & "# and Date2 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
textbox1.Text = .Fields("count2").Value
.Close()
else
.Open("select count(*) as count1 from Docstable where Date1 >=#" & DateTimePickerfrom.Value.Date & "# and Date1 <=#" & DateTimePickerto.Value.Date & "#", countcon, 2, 3)
textbox1.Text = .Fields("count1").Value
.Close()

end if

End With

If you're using .NET then you should use .NET and stop pretending that you're still using VB6. If you have a teacher or a boss who still thinks it's the 1980s then I guess you're out of luck but, otherwise, join the rest of us in 2020. Here's the same ADO.NET and pure SQL solution I provided at Stack Overflow:

vb.net Code:

Dim sql = "SELECT COUNT (*)
           FROM Docstable
           WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
           OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)"
Dim dateFrom = DatePickerFrom.Value.Date
Dim dateTo = DatePickerTo.Value.Date
Dim count As Integer
 
Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand(sql, connection)
    With command.Parameters
        .Add("@DateFrom1", OleDbType.Date).Value = dateFrom
        .Add("@DateFrom2", OleDbType.Date).Value = dateFrom
        .Add("@DateTo1", OleDbType.Date).Value = dateTo
        .Add("@DateTo2", OleDbType.Date).Value = dateTo
    End With
 
    connection.Open()
 
    count = CInt(command.ExecuteScalar())
End Using

I'm not 100% sure whether VB 2012 supports multiline String literals or not. If not, use an XML literal:

vb.net Code:

Dim sql = <sql>
              SELECT COUNT (*)
              FROM Docstable
              WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
              OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)
          </sql>
'...
 
Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand(sql.Value, connection)
    '...
End Using

BTW, you said on SO that you're using VB 2010 but here you say that you're using VB 2012. It may not matter in this case but please provide accurate information because it may be important.

Quote:

---

Originally Posted by jmcilhinney

BTW, you said on SO that you're using VB 2010 but here you say that you're using VB 2010. It may not matter in this case but please provide accurate information because it may be important.

---

Said VB 2010, and said VB 2010 on stackoverflow... :D Some kinda discrepancy there :D

Edit: Says VB 2012 here...

Quote:

---

Originally Posted by .paul.

Said VB 2010, and said VB 2010 on stackoverflow... :D Some kinda discrepancy there :D

Edit: Says VB 2012 here...

---

My outrage seems rather misplaced when i can't even type properly. :blush: Fixed my post.

Quote:

---

Originally Posted by jmcilhinney

If you're using .NET then you should use .NET and stop pretending that you're still using VB6. If you have a teacher or a boss who still thinks it's the 1980s then I guess you're out of luck but, otherwise, join the rest of us in 2020. Here's the same ADO.NET and pure SQL solution I provided at Stack Overflow:

vb.net Code:

Dim sql = "SELECT COUNT (*)
           WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1)
           OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)"
Dim dateFrom = DatePickerFrom.Value.Date
Dim dateTo = DatePickerTo.Value.Date
Dim count As Integer
 
Using connection As New OleDbConnection("connection string here"),
      command As New OleDbCommand(sql, connection)
    With command.Parameters
        .Add("@DateFrom1", OleDbType.Date).Value = dateFrom
        .Add("@DateFrom2", OleDbType.Date).Value = dateFrom
        .Add("@DateTo1", OleDbType.Date).Value = dateTo
        .Add("@DateTo2", OleDbType.Date).Value = dateTo
    End With
 
    connection.Open()
 
    count = CInt(command.ExecuteScalar())
End Using

---

Thanks for your effort my friend but it still gives me an error: "Additional information: Syntax error (missing operator) in query expression 'COUNT (*) WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1) OR (Date2 > Dateto1 AND Dateto2 BETWEEN @DateFrom2 AND @DateTo2)'."

Try this...

COUNT (*) WHERE (Date1 >= Date2 AND Date1 BETWEEN @DateFrom1 AND @DateTo1) OR (Date2 > Date1 AND Date2 BETWEEN @DateFrom2 AND @DateTo2)'."

I think a From is missing

so From what Table ?

COUNT(*) FROM whateveryourtableis ... etc

Yeah, oops. I missed the FROM clause but you really should have been able to work that out for yourself if you'd bothered to actually look at the SQL and try to figure out what the issue might have been. I have edited that post.

Quote:

---

Originally Posted by jmcilhinney

Yeah, oops. I missed the FROM clause but you really should have been able to work that out for yourself if you'd bothered to actually look at the SQL and try to figure out what the issue might have been. I have edited that post.

---

I missed it too. There were other errors in the sql string the op posted

Thanks for the correction Sir. Sorry if I'm giving you the confusion.