Selecting records between two dates

[jpinto]

Hello,

I'me trying to select the records that are on the database with the field 'Data" between two dates that are inserted into two fields: "DeData" and "aData". I've the following code but I'm getting an error on my SQL statment:

Private Sub DeData_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DeData.ValueChanged
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=""C:\Programaçao\Visual Basic\Inapal\ReportIt\ReportIt.mdb"";"
        con.Open()
        sSQL = "SELECT * FROM [RelatorioQuery] WHERE ((Data >'#" & DeData.Text & "#') AND (Data <'#" & aData.Text & "#')) AND (Descriçao LIKE'%" & SearchBox.Text & "%')"
        da = New OleDb.OleDbDataAdapter(sSQL, con)
        ds.Clear()
        da.Fill(ds, "RelatorioQuery")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "RelatorioQuery"
        con.Close()
    End Sub

Can annyone help me please?

Thanks,

João Pinto

 

[jebenson]

You are using both a single quote and the pound sign (#) to enclose your dates. Access uses only the pound sign. Change your SQL to this:

sSQL = "SELECT * FROM [RelatorioQuery] WHERE ((Data >#" & DeData.Text & "#) AND (Data <#" & aData.Text & "#)) AND (Descriçao LIKE '%" & SearchBox.Text & "%')"

Actually a better way to do this SQL is to use the BETWEEN statement, like so:

sSQL = "SELECT * FROM [RelatorioQuery] WHERE (Data [red]BETWEEN[/red] #" & DeData.Text & "# AND #" & aData.Text & "#) AND (Descriçao LIKE '%" & SearchBox.Text & "%')"

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[jpinto]

Thanks for your help.

In fact, the mistake that I had was the single quotes. Without it, it works fine.

João Pinto