openrecordset,sql expression

[len]

Why am I getting a run time error 3464?
Data Type mismatch in criteria expression

I am running a test query on an access database with the following code:
Static pubidno As Integer
Static stateno As String

pubidno = 100
stateno = "NY"

Dim dbs As Database, rst As Recordset
Set dbs = OpenDatabase("c:\program files\devstudio\vb\db1.mdb")
Set rst = dbs.OpenRecordset("SELECT * FROM Publishers " _
& "WHERE PubID < '" & pubidno & "' " _
& "AND State = '" & stateno & "' " _
& "ORDER BY Name ")
Set rst = Data1.Recordset
rst.Close

[Vlad Chapranov]

Delete quotes from your SQL statement: "WHERE PubID < " & pubidno & "....
Vlad

[Lothar Haensler]

looks like your Pubid is a numeric field. So get rid of the single quotes.
WHERE pubid < " & pubidno & " ...

[Lothar Haensler]

Oops, I just saw that you answered that question half an hour ago. I didn't see that (need to hit the refresh button more often).

[atumuluri]

Hi, here is the solution. You might have declared pubidno as integer. Hence no quotes are allowed on integer field. The is the final code:
-------------------------------------------
Static pubidno As Integer
Static stateno As String

pubidno = 100
stateno = "NY"

Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase("c:\program files\devstudio\vb\biblio.mdb")
Set rst = dbs.OpenRecordset("SELECT * FROM Publishers " _
& "WHERE PubID < " & pubidno _
& "AND State = '" & stateno & "' " _
& "ORDER BY Name ")

Debug.Print rst.Fields(0).Value
Debug.Print rst.Fields(1).Value
rst.Close
--------------------------------------------------
From:
Ashok