Problem with 'Like' SQL filter

Local time
Today, 03:34
Joined
Feb 14, 2025
Messages
63
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.

Code:
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
 
Remove
= " & "
 
.. and now test this: write in Me.SearchBox: ' or 'a' like ' ;)

=> to avoid sql injection use:
Code:
strSQL = "[TransDesc] Like '*" & replace(Me.SearchBox, "'", "''") & "*'"
 
Briefly explained with sample code to be on the safe side:

Code:
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:
Code:
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:
Code:
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.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom