How to include "Like" in a SQL Statement

[garyholc]

Hi

I have a form with a box on it where a user can type something, called searchbox.

I want to send a sql statement to a list box on another form, which will look up the name of a site, based on the text typed in the searchbox. I want to use the like statement, so it will search for the word in any part of the site name.

The sql text I have is:

sqltext = "SELECT Q_Search_All.RFC_Number, Q_Search_All.Version_Number, Q_Search_All.Date_Recd, Q_Search_All.Record_Number, Q_Search_All.IA_Status_Narr, Q_Search_All.Site_Name WHERE Q_Search_All.Site_Name like *" & Me.SearchBox & "* FROM Q_Search_All ORDER BY [RFC_Number], [Version_Number]; "

But it finds no records, even though loads of sites exist where the letters exist. I'm guessing I havent written the syntax right in the sql statement around the like clause.... any ideas what I have done wrong?

Thanks
Gary

 

[MSAccessRookie]

Try this:

In your code (Immediately after you have defined sqltext), display the value of sqltext in a msgbox. If this creates an error, break sqltext down into its pieces (exactly as you have them on the original line) in order to determine which piece is causing it to break. If you do not see the answer (and I suspect that you might), post your results and someone can comment.

I suspect that while it may not create any errors, it also may not be exactly what you think it should be.

 

[garyholc]

Yeah I did this already, didnt return any error. The message box says:

SELECT ... blah blah blah WHERE Q_Search_All.Site_Name like *abb* FROM Q_Search etc etc etc...

Gary

 

[MSAccessRookie]

Yeah I did this already, didnt return any error. The message box says:

SELECT ... blah blah blah WHERE Q_Search_All.Site_Name like *abb* FROM Q_Search etc etc etc...

Gary

If that is EXACTLY correct, then I think that you are missing some quotes (see the code below).

SELECT ... blah blah blah 
WHERE Q_Search_All.Site_Name like [SIZE=4][COLOR=red][B]"[/B][/COLOR][/SIZE]*abb*[B][SIZE=4][COLOR=red]"[/COLOR][/SIZE][/B] 
FROM Q_Search etc etc etc...

 

[garyholc]

Didnt work but fixed it by making a new query, then on the button which tells the form to open, I said forms!etc etc .listresults.rowsource = (name of new query)

and this works fine. Thanks anyway

 

[LPurvis]

FWIW in your original VBA string you'd not delimited the criteria.

sqltext = "SELECT Q_Search_All.RFC_Number, Q_Search_All.Version_Number, Q_Search_All.Date_Recd, Q_Search_All.Record_Number, Q_Search_All.IA_Status_Narr, Q_Search_All.Site_Name WHERE Q_Search_All.Site_Name like ""*" & Me.SearchBox & "*"" FROM Q_Search_All ORDER BY [RFC_Number], [Version_Number]; "

This was alluded to in subsequent posts - but not offered in the context of your VBA string.

Cheers.