SQL Like operator

I am searching to find like items, for instance Bath tub, Bath tub jetted, Bath tub white.

I use this sql to find like items:
sql = "SELECT DISTINCT ContractItems.txtItemDescription " & _
                     "FROM ContractItems " & _
                     "WHERE ContractItems.txtItemType  LIKE '" & msItemType & "%" & "'"

This works as long as the first word is bath.  If I type tub then 0 records found.  I want to be able to fine tub no matter where its located in the string even if it's"bathtub". What is the correct syntax to do that.  I've tried  ' "%" " & msItemType & "%" & "'" but that gives syntax error and I don't know if it will work with "baththb".

Thanks in advance
LVL 2
MoedAsked:
Who is Participating?
 
jdlambert1Connect With a Mentor Commented:
You've got the right idea, you just need your string variable to end up with:

WHERE ContractItems.txtItemType LIKE '%tub%'

So make sure msItemType doesn't have a single quote in it, and try this:

sql = "SELECT DISTINCT ContractItems.txtItemDescription " & _
                     "FROM ContractItems " & _
                     "WHERE ContractItems.txtItemType  LIKE '%" & msItemType & "%'"
0
 
MoedAuthor Commented:
Thats perfect.   Thanks a million.  I've only been programming for a few months and the proper placement of the ' and " and % and * drives me nuts.  
0
 
jdlambert1Commented:
Don't worry, when you've been at it longer, you'll find a lot more to drive you nuts.   ;)
0
All Courses

From novice to tech pro — start learning today.