Problem with 'Like' SQL filter

[ChrisLeicester]

Hi All

I am trying to do a search button on a continuous form to search for records that contain some of what is entered in the textbox.

Private Sub SearchBTN_Click()

Dim strSQL As String

strSQL = "[TransDesc] = " & "Like '*" & Me.SearchBox & "*'"

Me.Filter = strSQL

Me.FilterOn = True


End Sub

When running I get a syntax error saying missing operator.

Printing the strSQL in the imediate window shows;

[TransDesc] = Like '*zilch*'

which to me looks right. I do have the [TransDesc] field on the form

What have I done wrong now

Thanks in advance

Chris

 

[XPS35]

You need to remove the =.

 

[MajP]

Remove
= " & "

 

[ChrisLeicester]

Hi
Perfect, is working great now

Thanks a lot

 

[Josef P.]

.. and now test this: write in Me.SearchBox: ' or 'a' like '

=> to avoid sql injection use:

strSQL = "[TransDesc] Like '*" & replace(Me.SearchBox, "'", "''") & "*'"

 

[ChrisLeicester]

Josef
your way works better, as you say. searching 'A' returns another syntax error, but your line doesnt.
Thanks

 

[Josef P.]

Briefly explained with sample code to be on the safe side:

Dim SearchBoxStringInsertedByUser as String
SearchBoxStringInsertedByUser = "abc"
Debug.Print "User insered: "; SearchBoxStringInsertedByUser

Dim strSQL As String
strSQL = "[TransDesc] = '*"  & SearchBoxStringInsertedByUser & "*'"
Debug.Print strSQL

=> [TransDesc] = '*abc*' => as expected
But:

Dim SearchBoxStringInsertedByUser as String
SearchBoxStringInsertedByUser = "' or 'a' like '"
Debug.Print "User insered: "; SearchBoxStringInsertedByUser

Dim strSQL As String
strSQL = "[TransDesc] = '*"  & SearchBoxStringInsertedByUser & "*'"
Debug.Print strSQL

=> [TransDesc] = '*' or 'a' like '*'
SQL is correct, but I don't think that was the developer's expected result.
'a' like '*' is alway true.

Fix it:

Dim SearchBoxStringInsertedByUser as String
SearchBoxStringInsertedByUser = "' or 'a' like '"
Debug.Print "User insered: "; SearchBoxStringInsertedByUser

Dim strSQL As String
strSQL = "[TransDesc] = '*"  & Replace(SearchBoxStringInsertedByUser, "'", "''") & "*'"
Debug.Print strSQL

=> [TransDesc] = '*'' or ''a'' like ''*'
This is exactly 1 filter expression with one filter value.