wild card search in VBA

[Eric the Viking]

Dear All
Im confused again and need advice.
I have a text box with vba in the on update event to populate a list box.
The code is as follows:

Private Sub TextSurname_AfterUpdate()

If IsNumeric(Me.TextSurname) Then
ListPts.RowSource = "Select [tbl-patient details].[Patient number],[tbl-patient details].Surname,[tbl-patient details].[First name 1]," & _
"[tbl-patient details].[Date of Birth],[tbl-patient details].[NHSNumber]" & _
"FROM [tbl-patient details] " & _
"WHERE [tbl-patient details].[Patient number] = " & TextSurname.Value & _
" ORDER BY [tbl-patient details].[First name 1];"
Else
ListPts.RowSource = "Select [tbl-patient details].[Patient number],[tbl-patient details].Surname,[tbl-patient details].[First name 1]," & _
"[tbl-patient details].[Date of Birth],[tbl-patient details].[NHSNumber]" & _
"FROM [tbl-patient details] " & _
"WHERE [tbl-patient details].Surname = '" & TextSurname.Value & "' " & _
"ORDER BY [tbl-patient details].[First name 1];"

_
End If

Dim getListPtsCount As Integer
Dim setHeight As Integer
getListPtsCount = Me.ListPts.ListCount
setHeight = Me.ListPts.Height
If getListPtsCount < 20 Then
Me.ListPts.Height = getListPtsCount * setHeight
Else
Me.ListPts.Height = setHeight * 20
End If

End Sub

I wish to look up based on a phrase or part of a surname but I have not been able to get "Like"*"& ...... &"*" to work. Is there a trick to this?

Regards

Eric:banghead:

 

[TJPoorman]

If you just want the like function to work with text then try something like this in your where statement:

"WHERE [tbl-patient details].Surname Like '*" & TextSurname.Value & "*' " & _

 

[Eric the Viking]

Perfect works a treat as ever I cant see why I didn't think this through doh.

I am most grateful.