Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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?
  • 2
1 Solution
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 & "*'
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?
NorieData ProcessorCommented:
How will the field to be searched be determined?

If you used 2 textboxes you could use something like this to filter by or as criteria.
If IsNull(txtSubject) Then
      strFilter = "subjectlast Like '*'"
     strFilter = "subjectlast Like '*" & txtSubject.Value & "*'"
End If

strFilter = strFilter & " Or "

If IsNull(txtCaseNo) Then
      strFilter = strFilter & " casenumber Like '*'"
     strFilter = strFilter & " casenumber Like '*" & txtCaseNo.Value & "*'"
End If

Open in new window

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
Commad1=> Command3

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now