error on refresh

[stan yost]

I am trying to refresh a data control but I keep getting the following message no matter what I try:
Run-time error 3464
Data type mismatch in criteria expression.
If I leave the where clause out I don't get the error message.

Private Sub cmdseektask_Click()
Dim strsql As String, temp As Long
Let temp = InputBox("enter a bidnumber:")
Let strsql = "SELECT * from bids where bidno ='" & temp & "'"
Let datbids.RecordSource = strsql
datbids.Refresh

End Sub

Please help.:?:

[Comatose]

Let strsql = "SELECT * from bids where bidno ='" & temp & "'" is not the solution, try something like: strsql = "SELECT * from bids where bidno = " & chr(34) & chr(34) & temp & chr(34) & "'" & chr(34)

EDIT: Nevermind, I totally missed the mark on that one! :eek: try something like this (I don't know what bidno is supposed to be, either an integer, or a long, or a double, or what): Let strsql = "SELECT * from bids where bidno ='" & cint(temp) & "'"

[stan yost]

Let strsql = "SELECT * from bids where bidno ='" & temp & "'" is not the solution, try something like: strsql = "SELECT * from bids where bidno = " & chr(34) & chr(34) & temp & chr(34) & "'" & chr(34)

EDIT: Nevermind, I totally missed the mark on that one! :eek: try something like this (I don't know what bidno is supposed to be, either an integer, or a long, or a double, or what): Let strsql = "SELECT * from bids where bidno ='" & cint(temp) & "'"

Comatose - In order to simplify things as much as possible I created a new folder on my C drive named testdatabase in which I put a database created in Access named db3. This contains a table named bidtest that has 2 fields, bidnumber and bidtask. Bidnumber is autonumber and bidtask is text. I put a few records in the table and then created a form in VB 5.0 that has a datacontrol named dattestbids and pointed it to the db3 database and the bidtest table as the recordsource. The form also has a text box that points to the table and field bidtask. I have a command button that executes the following sub:

[Private Sub cmdscroll_click()
Set dbsbidinfo = OpenDatabase("c:\testdatabase\db3.mdb")
Set rstbidinfo = _
dbsbidinfo.OpenRecordset("bidtest", dbOpenDynaset)

Dim strsql As String

' Let strsql = "select * from bidtest"
Let strsql = "SELECT * from bidtest where bidnumber ='" & CInt(temp) & "'"
Let dattestbids.RecordSource = strsql
dattestbids.Refresh
End Sub]

The code works until I put in the where clause. When I put the where clause in I get the message : "data type mismatch in criteria expression"
Can you help me with this?

[aparnesh]

Remove the Single Quotes from the criteria expression. It should be

Let strsql = "SELECT * from bidtest where bidnumber = " & CInt(temp)

Single Quotes are used for String & Date Datatypes, Not Numbers. Also since you have declared temp as a Long, u shouldn't use CInt. For safety sake, u can use

temp = val(InputBox("...."))

Also you should check if the user has input a number by using IsNumeric Function.

[stan yost]

Thank you very much!