Issue: error '3075' Syntax error (missing operator) access 2013

Question

Can any of you please tell me what's wrong with my codes:

Private Sub Command379_Click

Dim strsearch as String

Dim strText as String

strText = Me.TxtSearch.Value

strsearch = "Select * from qryInfo 
where ((Supplier Name LIKE ""*" & strText & "*"") 
OR (Type LIKE ""*" & strText & "*""))"


Me.RecordSource = strsearch

End Sub

Thanks a lot!

Answer 1

If you want to match text which include double quotation mark please follow here:

text:

"abc"

text in VBA:

"""" & "abc" & """" (4 double quotation marks are necessary)

Thank you.

Answer 2

Following should be considered:

• If you have fieldnames with a blank inside like Supplier Name then you would have to surround it with square brackets: [Supplier Name].
• The brackets () are not really necessary in your case. You can omit them.
• Instead of using two quotation marks (""), in order to add a literal quotation mark, you can use a single quote (') instead, what makes it more clear to read.
• Type is a reserved word. If possible you shouldn't use it. Minimum would be to also surround it with square brackets ([Type]).

• If your SQL string really is splitted in several lines in VBA, then you would have to handle it explicitely by using underscores (_) and string concatenation:

  strTest = "This is a string" & _
            " defined in several" & _
            " lines of code in VBA."
  

  Alternatively you could use this:

  strTest = "This is a string"
  strTest = strTest & " defined in several"
  strTest = strTest & " lines of code in VBA."
  

So finally your (single line) SQL containing VBA would be this:

strSearch = "Select * From qryInfo Where [Supplier Name] Like '*" & strText & "*' Or [Type] Like '*" & strText & "*'"

Important too:

Be aware that your current approach, using string concatenation, doesn't avoid SQL injection, which is a real security problem!

Take a look here on how to handle it better (for example by using parameter queries in VBA): How do I use parameters in VBA in the different contexts in Microsoft Access?

Answer 3

Having nothing but wildcards is the same as having no WHERE criteria at all. So you don't need a WHERE statement.

…...having said that - I would recommend you create the query using the Query Design View and then once it returns the records as you consider correct - then use the option to display it in SQL View. This will give you the syntax.