Search Query not searching appropriately

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Search Query not searching appropriately

Post by adam »

I'm using the following code to search text in the criteria. There are instances where I need to type part of the text and the code has to search the text. However its not doing the needful.

For example: if the user writes apple instead of red apple it does not search the word. but if the user writes red it searches the word.

Is there any way I could achieve this?

Code: Select all

    var = Me.txtSearch
    Set cnn = New ADODB.Connection

    If CheckBox2 = True Then
        strSQL = "SELECT [Code],[Cat] FROM Dummy WHERE [Code] = '" & var & "' OR [Cat] = '" & var & "'"
        
    Else
        strSQL = "SELECT [Code],[Cat] FROM Dummy WHERE [Code] LIKE '" & var & "%' OR [Cat] LIKE '" & var & "%'"
    End If
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search Query not searching appropriately

Post by HansV »

Code: Select all

    If CheckBox2 = True Then
        strSQL = "SELECT [Code],[Cat] FROM Dummy WHERE [Code] = '" & var & "' OR [Cat] = '" & var & "'"
        
    Else
        strSQL = "SELECT [Code],[Cat] FROM Dummy WHERE [Code] LIKE '%" & var & "%' OR [Cat] LIKE '%" & var & "%'"
    End If
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Search Query not searching appropriately

Post by adam »

Thanks a lot for the help Hans. It worked really well.
Best Regards,
Adam