What's wrong with this 'find record' code?

[Guest]

Hi guys,
Can you tell me why I'm getting an error message saying "Syntax error
(missing operator) in expression" ?

Dim rst As Recordset
Dim Criteria As String
Dim searchvalue As String

searchvalue = InputBox("Please enter all or part of the candidate's Surname
in the box below.", "Search by Surname")
If searchvalue = "" Then
Exit Sub
End If

Criteria = "SURNAME Like *" & searchvalue & "*"
Set rst = Me.RecordsetClone
rst.FindFirst Criteria
If rst.NoMatch Then
MsgBox "Sorry, no match found. Try again."
Else
Me.Bookmark = rst.Bookmark
Me.txtSurname.SetFocus
End If

Exit_cmdFind_Click:
Exit Sub

Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click

Thanks very much for any help you can give.

Regards,

Lee

 

[Douglas J Steele]

Since Surname is a text field, you need quotes around the value you're
looking for:

Criteria = "SURNAME Like " & Chr$(34) & "*" & searchvalue & "*" & Chr$(34)

(Chr$(34) is ")

If were anything other than names, you could probably get away with

Criteria = "SURNAME Like '*" & searchvalue & "*'"

where, exagerated for clarity, that's

Criteria = "SURNAME Like ' * " & searchvalue & " * ' "

However, that will fail on names with apostrophes in them, like O'Reilly.

 

[Guest]

Thanks Doug, that works a treat now.
I had previously tried the use of apostrophes in the code but it didn't
work. I shall make a note of the ascii code for the quotes character as I
expect I shall need to use this again sometime.

Regards,

Lee

 

[Douglas J Steele]

You don't actually need to use the ASCII code: you can simply put two double
quotes where you want a single double quote to appear in your string:

Criteria = "SURNAME Like """*" & searchvalue & "*"""

I just find it easier to read using Chr$(34)