Type Mismatch Error: New to access programming

  • Thread starter Thread starter Neal Ostrander
  • Start date Start date
N

Neal Ostrander

I am trying to learn how to uses class modules in access 07. I have the
following code and it is giving me a Data type mismatch in criteria
expression.
Thanks in advance for any help
Neal


Dim value As String

value = InputBox("Enter ISBN to search for.", "Find Book")

newBook.FindBook value

End Sub

******************** below is in class module

Public Sub FindBook(ByVal value As String)

'declare variables
Dim bookID As String
Dim rsBook As New ADODB.Recordset
Dim strSQL As String
'assign value entered by user to bookID variable
bookID = value
'build sql string based on value entered by user
strSQL = "SELECT * FROM Books WHERE ISBN = " & bookID
'open recordset using the sql string
rsBook.Open strSQL, CurrentProject.AccessConnection, adOpenDynamic,
adLockOptimistic 'error occurs here

'if not eof assign values to global variables
If rsBook.EOF = False Then

bookTitle = rsBook!title
bookISBN = rsBook!isbn
bookDate = rsBook!PublishDate
bookPrice = rsBook!price

End If
'close recordset
rsBook.Close

MsgBox "Title: " & bookTitle & Chr(13) & "ISBN: " & bookISBN & Chr(13) &
"Published: " & bookDate & Chr(13) & "Price: " & bookPrice

End Sub
 
Since bookId is a string, it needs to be delimited with quote marks, thus:

strSQL = "SELECT * FROM Books WHERE ISBN = '" & bookID & "'"
 
You need single quotes around the literal value, otherwise SQL thinks it is a
variable name to compare to.
It is the different between
SELECT * FROM sometable WHERE a = b
&
SELECT * FROM sometable WHERE a = 'b'

FYI: I didn't see why you actually need to declare bookID as a new variable.
You can just reuse VALUE from your function declaration, unless you are
intending to modify BookID and need to retain the original VALUE somewhere
else.

Hope that helps.
 
Back
Top