0

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)


            Try

            reportconn.ConnectionString = reportstring
            reportconn.Open()
        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
            .Fill(USERTABLE)
            End With

            main.ListView1.Items.Clear()
        For i = 0 To USERTABLE.Rows.Count - 1
            With main.ListView1
                .Items.Add(USERTABLE.Rows(i)("ID"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(USERTABLE.Rows(i)("PropertyNumber"))
                    .Add(USERTABLE.Rows(i)("Article"))
                    .Add(USERTABLE.Rows(i)("Description"))
                    .Add(USERTABLE.Rows(i)("Unitofmeasure"))
                    .Add(USERTABLE.Rows(i)("UnitValue"))
                    .Add(USERTABLE.Rows(i)("Remarks"))
                    .Add(USERTABLE.Rows(i)("DateofAcquisition"))
                    .Add(USERTABLE.Rows(i)("BalanceperCardQTY"))
                    .Add(USERTABLE.Rows(i)("OnhandpercountQTY"))
                    .Add(USERTABLE.Rows(i)("TotalAmount"))
                End With
            End With
        Next



            Catch ex As Exception
            MsgBox(ex.Message)
            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

2
Contributors
1
Reply
17
Views
3 Years
Discussion Span
Last Post by G_Waddell
0

Hi,

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?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.