Date before sql

[akamini]

I was wondering whether anyone could tell em whats wrong with this code.

Its trying to get the records where the theorydates (stored in a database as shorttime) are before 2 years ago and return the records in a datagrid but it doesnt.

Private Sub ExpiredTheoryTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New OleDb.OleDbConnection
        Dim OleDBCon As System.Data.OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim sql As String
        Dim theorydateexpired As Date



        theorydateexpired = DateAdd(DateInterval.Year, -2, Now)

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \DrivingSchool.mdb"
        sql = "SELECT * FROM tblStudents WHERE theorydate >= " & theorydateexpired
        OleDBCon = New System.Data.OleDb.OleDbConnection(con.ConnectionString)
        ' sql is given a value by the if statements

        da = New System.Data.OleDb.OleDbDataAdapter(sql, OleDBCon)
        OleDBCon.Open()
        da.Fill(ds, "theorydateexpire")
        DataGridView1.DataSource = ds.Tables("theorydateexpire")
    End Sub
End Class

[apegram]

Dates need to be delimitted, like strings. In Access, the standard delimitter for a date is the pound (#) sign.

sql = "SELECT * FROM tblStudents WHERE theorydate >= #" & theorydateexpired & "#"

The other option is to use a parameterized query.

sql = "SELECT * FROM tblStudents WHERE theorydate >= @theorydate"
        Dim dateParamater As New OleDb.OleDbParameter("@theorydate", OleDb.OleDbType.Date)
        dateParamater.Value = theorydateexpired
        da.SelectCommand.Parameters.Add(dateParamater)

This code would be after you've instantiated your OleDbDataAdapter (da).

Also, if I'm reading your request properly, do you want students where the dates are prior to 2 years ago? If so, reverse your comparison. You're pulling greater than/equal to.