Using a WildCard in MS query

[kgkev]

I have a query that returns data based on the contents of Cell A2 (Product code)

My Query Criteria currently looks like
Product = [PROD]

PROD is populated by A2 and refreshes on change.

I now want to change this to a wildcard search.

If I directly change this on the
Product like %PCB%
it returns the date I wanted.


However I still want the user to be able to enter the criteria.

I have tried


Product like %[PROD]%
and
Product like [PROD]
Where [PROD] in A2 = "%"&A1&"%"

Neither of these options are working. I am sure option 2 has worked in the past but maybe I have the syntax wrong.

Any Suggestions?

 

[John_w]

I don't think you can use a cell parameter and a wildcard at the same time in MS Query.

This can be done with VBA code in the Worksheet_Change event handler which modifies the "LIKE '%xxx%'" part of the SQL query (the Command Text) to change the xxx part to cell A2's current value and refreshes the query.

Create your query initially with a specific wildcard search, e.g. "Product like '%PCB%'" - note the single quotes surrounding %PCB%, and then add this to the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim qt As QueryTable
    Dim p1 As Long, p2 As Long
    
    If Target.Address = "$A$2" Then
        Set qt = Me.ListObjects(1).QueryTable
        With qt
            p1 = InStr(1, .CommandText, " LIKE '%", vbTextCompare)
            If p1 > 0 Then
                p1 = p1 + Len(" LIKE '%")
                p2 = InStr(p1 + 1, .CommandText, "%'")
                .CommandText = Left(.CommandText, p1 - 1) & Me.Range("A2").Value & Mid(.CommandText, p2)
                Application.EnableEvents = False
                .Refresh BackgroundQuery:=False
                Application.EnableEvents = True
            End If
        End With
    End If
        
End Sub

Now change A2's value and the query will update and refresh.

 

[John_w]

It seems I was wrong and you can use a cell parameter and wildcards in the query, therefore you don't need the above VBA code. Try this in the SQL command text:

Like '%'+?+'%'

 

[kgkev]

Hi John,

I tried every combination of Like '%'+?+'%' without success, it never returned any rows.

The VBA solution however worked perfectly.
Thank you very much.