hello! i want to display all the transactions within a month...but an error occured says...
data type mismatch in criteria expression...pls help me..here is my initial code...

If rs.State = adStateOpen Then rs.Close
   rs.Open "Select * from tblsupplier where date between '" & DTPicker1.Value & "' And '" & DTPicker2.Value & "';", cn, adOpenKeyset, adLockPessimistic
While Not rs.EOF
        With frmlist.ListView1.ListItems.Add(, , rs!supplier)
            With .ListSubItems
                .Add , , rs!nmesup
                .Add , , rs!category
                .Add , , rs!nmeitem
                .Add , , rs!quantity
                .Add , , rs!spec
                .Add , , rs!amount
                .Add , , rs!tamount
            End With
        End With
        rs.MoveNext
    Wend

Recommended Answers

All 4 Replies

you need to format the dates properly before passing the same into the database.

Agreed, though I have done most work in DAO not ADO or SQL...

This error, though not as bad as the illegal "Null" error, usually requires a type conversion when going into the RecordSet.

And, sometimes when retrieving, as well.

Just out of curiosity, are you going against an MSAccess database, and is your error getting thrown on the "rs.Open" statement? If so, you might be running across a delimiter error. In MSAccess SQL, you have to use # as your delimiter around dates, rather than a single-quote. So your statement would look like:

rs.Open "Select * from tblsupplier where date between #" & DTPicker1.Value & "# And #" & DTPicker2.Value & "#;", cn, adOpenKeyset, adLockPessimistic

If you aren't using MSAccess as your database, then nevermind.

Good luck!

to BitBlt : thank u so much sir...it works..i owe you for this...thank u very much and God Bless to you...and continue for being a blessing to evryone...

to debasisdas and debasisdas : thank u sir...i appreciate your reply..God Bless to both of u and continue for being a blessing to evryone...

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.