Parital Search function

[cgemmill1]

I have built a search form using the VBA code below.

The problem is the search fucntion requires only returns a search for complete answers. Example if I have Steve Smith and seach for either Steve or Smith I get no results. When I include the entire name it finds a match. How can I write the code to conduct partial searches.

Here is my code

Option Compare Database
Private Sub cmdClear_Click()
Me.Infotbl_subform.Form.RecordSource = "SELECT * FROM Hospital_communication "
Me.Infotbl_subform.Requery
txtID = ""
txtcontact_name = ""
txtfocus = ""
DateFrom = ""
datto = ""
txtMedicareId.SetFocus
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdSearch_Click()
On erorr GoTo errr
Me.Infotbl_subform.Form.RecordSource = "SELECT * FROM Hospital_communication " & BuildFilter
Me.Infotbl_subform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"

Const conJetDate = "\#dd\/mm\/yyyy\#"

varWhere = Null


If Me.txtcontact_name > "" Then
varWhere = varWhere & "[contact_name] like " & tmp & Me.txtcontact_name & tmp & " AND "
End If

If Me.txtMedicareId > "" Then
varWhere = varWhere & "[MedicareId] like " & Me.txtMedicareId & " AND "
End If

If Me.txtfocus > "" Then
varWhere = varWhere & "[Focus_Area] like " & tmp & Me.txtfocus & tmp & " AND "
End If


If Me.txtDateFrom > "" Then
varWhere = varWhere & "([Contact_date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

If Me.txtDateTo > "" Then
varWhere = varWhere & "([Contact_date] <= " & Format(Me.txtDateTo, conJetDate) & ") AND "
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere

End Function
Private Sub Form_Current()
End Sub
Private Sub Form_Load()
cmdClear_Click
End Sub

 

[jdraw]

see the video tutorial
http://www.datapigtechnologies.com/flashfiles/searchform.html