data type mismatch....

[mitchney]

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

[debasisdas]

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

[SpiritualMadMan]

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.

[BitBlt]

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!

[mitchney]

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...