I want to filter the values in the listview using the date range selected in two datetime picker.
Here is my code:

 Dim reportstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Fe\Desktop\SADsystem\SADsystem\stockroom.accdb;Jet OLEDB:Database Password=stockroom"
        Dim reportconn As New OleDbConnection

        Dim date1 As Date
        Dim date2 As Date

        date1 = Convert.ToDateTime(DateTimePicker1.Value)
        date2 = Convert.ToDateTime(DateTimePicker2.Value)


            reportconn.ConnectionString = reportstring
        Dim strCmd As String = "SELECT * FROM itemequipment WHERE DateofAcquisition BETWEEN '" & _
        Format(date1, "MM/dd/yyyy") & "' AND '" & _
        Format(date2, "MM/dd/yyyy") & "' ORDER BY DateofAcquisition ASC;"

        Dim SqlCommand As New OleDbCommand
            Dim SqlAdapter As New OleDbDataAdapter
        Dim USERTABLE As New DataTable

            With SqlCommand
                .CommandText = strCmd
            .Connection = reportconn
            '.Parameters.AddWithValue("@dt1", DateTimePicker1.Text.Trim)
            '.Parameters.AddWithValue("@dt2", DateTimePicker2.Text.Trim)

            End With

            With SqlAdapter
                .SelectCommand = SqlCommand
            End With

        For i = 0 To USERTABLE.Rows.Count - 1
            With main.ListView1
                With .Items(.Items.Count - 1).SubItems
                End With
            End With

            Catch ex As Exception
            End Try
        End If

it displays an error "Data type mismatch in criteria expression." The data type of DateofAcquisition in my database is set to date/time in short format. Please help me fix my code


Put a break point in where you generate your sql query and a watch on the SQL query. Now copy the query text into Access querybuilder in SQL mode - Run the query in Access - does it work? Also does Access Edit/change your query?

Be a part of the DaniWeb community

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