Errors 2448 and 3075!

[Confused87]

I have a search comand button and it works most of the time, excpet with one
name (so far) O'Shea. It comes up with the following error message:

Run- time error '2448' You can't assign a value to this object. The debugger
highlights this: Me.Filter = "[Surname] LIKE '*" & RetVal & "*'"

However othertimes, it says this:
Runt-time error '3075'
Syntax error (missing operator) in query expression "[Surname] LIKE
'*O'Shea*".

Any ideas? Here's the whole section:

Private Sub Command187_Click()
Dim RetVal As String
RetVal = InputBox("Enter Surname")

If RetVal <> "" Then
Me.Filter = "[Surname] LIKE '*" & RetVal & "*'"
Me.FilterOn = True
End If


End Sub

Many Thanks
C

 

[Stefan Hoffmann]

hi,

I have a search comand button and it works most of the time, excpet with one
name (so far) O'Shea. It comes up with the following error message:

Run- time error '2448' You can't assign a value to this object. The debugger
highlights this: Me.Filter = "[Surname] LIKE '*"& RetVal& "*'"

This is hard to solve. Is it reproduce-able?

However othertimes, it says this:
Runt-time error '3075'
Syntax error (missing operator) in query expression "[Surname] LIKE
'*O'Shea*".

This one should be obvious: You need to escape your strings. The
resulting SQL string in you case is

[Surname] LIKE '*O'

followed by the non SQL command Shea*.

Use Replace() to escape the single quotation mark.

Any ideas? Here's the whole section:

Rewrite it:

Private Sub Command187_Click()

On Local Error GoTo LocalError

Dim RetVal As String
RetVal = Trim(InputBox("Enter Surname"))

If RetVal <> "" Then
Me.Filter = "[Surname] LIKE '*" & Replace(RetVal, "'", "''") & "*'"
Me.FilterOn = True
End If

Exit Sub

LocalError:
MsgBox "Error while filtering for '" & RetVal & "'." & VbCrLf & _
Err.Number & " " & Err.Description

End Sub


mfG
--> stefan <--