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

[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:

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

[ronelpisan]

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:

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)'."