This may not be of any use, but I created a search form, looking at one of two fields, one a number, one a text field. It just has an unbound text box and a search command button. Text is on command button. Hopefully, you may be able to play around with it to suit your needs.
(FYI in the following:
frmPatientDisplay = form it's searching for the data through;
PtLName = txt field;
UnitNo = numeric field)
Private Sub cmdSearch_Click()
Dim r As Recordset
Dim SrchUnit As Long
Dim SrchName As String
Dim SrchLen As Integer
Dim FindData, SU, SN As Boolean
FindData = True
SU = False
SN = False
If IsNumeric(SearchTxt.Value) Then
SrchUnit = Val(SearchTxt.Value)
SU = True
Else
SrchName = SearchTxt.Value
SrchLen = Len(SrchName)
SN = True
End If
Set r = Forms("frmPatientDisplay").RecordsetClone
r.MoveFirst
Do While Not r.EOF
If SU Then
If r.Fields("UnitNo") = SrchUnit Then
FindData = False
Exit Do
End If
Else
If UCase(Left(r.Fields("LName"), SrchLen)) = UCase(SrchName) Then
FindData = False
Exit Do
End If
End If
r.MoveNext
Loop
If FindData Then
msgbox ("No match was found for your search.")
Else
Forms("frmPatientDisplay").Bookmark = r.Bookmark
End If
DoCmd.Close
r.Close
End Sub
[This message has been edited by Ally (edited 11-02-2001).]
