asked on

How can I search two fields from one table at one time?

I need to search a table called tblsubjects, for either by the name (field subjectlast) or case number (field casenumber) I want to use a text box on my form to enter the search criteria. After the criteria has been entered I would like to populate a sub form with the results. i would like the search to be a close match, it does not have to be and exact match. Any ideas how to get started in the right direction?

ErezMor

you dont need a sub form, you can use a continuous form.
on the form's header , put an unbound textbox (say you call it txtFilter) for the user to fill the search criteria, and a command button "Search"
now write this code in the Click event of the button:
if txtFilter<>"" then
Me.Filter="SubjectLast Like '*" & txtFilter & "*'
Me.FilterOn=True
End If

now, i didnt understand the other field's thing (CaseNumber), the same search term is for both at the same time or the user needs to choose wether to search for matching SubjectLast OR CaseNumbers?

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

Try this:
Code filters Folder names if either FolderFrom or FolderTo (a copy utility) contanis characters typed in Text1 box.
Subform control name is B.
The code is in the Command1_Click event on the main form.

Private Sub Command3_Click()
B.Form.FilterOn = False
B.Form.Filter = "FolderFrom Like '*' & text1 & '*' OR FolderTo Like '*' & text1 & '*'"
B.Form.FilterOn = True
End Sub

Hamed Nasr

Commad1=> Command3