im a complete newbie in visual basic and im currently working on my first project which is the book borrowing system. the project is almost done except with this error.

Syntax error(missing operator) in query expression 'Title like %Mathew Henry's Commentary:GEnesis to Revelation%"

Re: Syntax Error 80 80

Please post the entire query.

Re: Syntax Error 80 80

Here is the complete query of the error.
I have a combobox, a txtbox namely txtsearch and a datalistfield in which all the titles of the book will appear.

Private Sub txtsearchbook_Change()

If cmbsearchbook.Text <> "" Then
adobooksearch.RecordSource = "SELECT * from tblbooks where " & cmbsearchbook & " like '%" & txtsearchbook.Text & "%'"
adobooksearch.Refresh
Else
adobooksearch.RecordSource = "SELECT * from tblbooks" 'where Author like '%" & "#" & "%'"
adobooksearch.Refresh
End If
End Sub
Re: Syntax Error 80 80

What you posted was the code that generates the query. What I want to see is the query that is causing the error.

Re: Syntax Error 80 80

HERE:

adobooksearch.Refresh

Re: Syntax Error 80 80

Hi

It looks like your WHERE criteria contains a value with an apostraphe so your SQL statement is breaking as the criteria itself needs to be enclosed within apostraphes.

You can either use parameterised queries or escape your search criteria. For example "SELECT * from tblbooks where " & cmbsearchbook & " like '%" & Replace(txtsearchbook.Text, "'", "''") & "%'". Note how the replace function has been used to take one apostraphe and replace it with two, thus escaping it.

HTH

Re: Syntax Error 80 80

This is a query

SELECT * FROM tblbooks WHERE author LIKE 'Smi%'

This is not a query

adobooksearch.Refresh

If you want help debugging the query then post your query.

Re: Syntax Error 80 80

It works djjeavons.
Thanks a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.