Member Avatar for 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.

Edited by akamini because: n/a
  • 2 Contributors
  • 2 Replies
  • 131 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by akamini
Member Avatar for kvprajapati 
str="SELECT * FROM tblBookings WHERE Lessondate>=#" & BookingsWeekStart & "# And Lessondate<=#" & BookingsWeekEnd & "#"
commented: Thank you again! +1
Member Avatar for akamini

Thank you again


I also changed it to

BookingsWeekStart = Format(Now, "dd/MM/yyyy")
        BookingsWeekEnd = DateAdd(DateInterval.Day, 7, Now)
commented: Cool! +7
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.