SQL between dates

[akamini]

Yet another date time related sql statement i need help with, oh how I loathe them.

I need to find the records between now and a week ahead.

I have this so far

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 BookingsWeekStart As Date
        Dim BookingsWeekEnd As Date
        BookingsWeekStart = Now
        BookingsWeekEnd = DateAdd(DateInterval.Day, 7, Now)

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \DrivingSchool.mdb"
        sql = "SELECT * FROM tblBookings WHERE Lessondate Between #" & BookingsWeekStart & "#" And "#" & BookingsWeekEnd & "#"

        OleDBCon = New System.Data.OleDb.OleDbConnection(con.ConnectionString)


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

        DataGridView1.Sort(DataGridView1.Columns("LessonDate"), System.ComponentModel.ListSortDirection.Ascending)

comes up with Conversion from string "SELECT * FROM tblBookings WHERE " to type 'Long' is not valid. error.

[kvprajapati]

str="SELECT * FROM tblBookings WHERE Lessondate>=#" & BookingsWeekStart & "# And Lessondate<=#" & BookingsWeekEnd & "#"

[akamini]

Thank you again

I also changed it to

BookingsWeekStart = Format(Now, "dd/MM/yyyy")
        BookingsWeekEnd = DateAdd(DateInterval.Day, 7, Now)