How to do a search for like *criteria* in vba?

[copleyuk]

I'm using the following to builder a search criteria:

Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.frmsubClients.Form.RecordSource = "SELECT * FROM qryClientData " & BuildFilter
    
    ' Requery the subform
    Me.frmsubClients.Requery
End Sub

And then using this to set the filter:

Private Function BuildFilter() As Variant
    Dim Varwhere As Variant

If Me.Reference > "" Then
        Varwhere = Varwhere & "[referencenumber] LIKE """ & Me.Reference & "*"" AND "
    End If

This works great for searches where the wildcard follows the search criteria. However, no matter how I juggle it I cannot get the search to work for a contains criteria. ie

If Me.Reference > "" Then
        Varwhere = Varwhere & "[referencenumber] LIKE "*"" & Me.Reference & "*"" AND "

Can anyone point out where I am going wrong?

Thanks :)

[Delerna]

have you tried it like this
I bolded it where it is different to yours

LIKE [B]""*" [/B]& Me.Reference & "*"" AND "

If that doesn't work then maybe something like this

LIKE [B]""" & "*"[/B] & Me.Reference & "*"" AND "

or

LIKE [B]" & chr(34) & "*" [/B]& Me.Reference & "*"" AND "

It's just a matter of getting the correct number of quotations
and the * in the right place

You had the first * in the wrong place
It would have read as this to the Query engine

   like * "test string*"

but you mean

   like "*test string*"

[copleyuk]

First one worked a treat!!

Thanks very much :)

[NeoPa]

I suspect you'd find it easier (or harder to make mistakes) using ANSI Standards in String Comparisons.