Prev: Combox not getting into edit mode
Next: Populate a form based on a query using parameters
From: Confused87 on 19 Apr 2010 07:19
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
From: Stefan Hoffmann on 19 Apr 2010 07:37
hi,

On 19.04.2010 13:19, Confused87 wrote:
> 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 <--
 | 
Pages: 1
Prev: Combox not getting into edit mode
Next: Populate a form based on a query using parameters