Search data from multiple fields using operator “OR” with the field listbox and link from another table

MS Access Questions and AnswersCategory: Access VBASearch data from multiple fields using operator “OR” with the field listbox and link from another table
Gnoy District Hospital asked 4 months ago

Hello iaccessworld term,
I have created a database for patient’s information at the hospital. II have create a form to search patient’s information. And the form source from the table as fields: PatientsID, PatientFullname, PatientDOB, PJob, PatientVillage…..The field of PatientVillage data type is number and that field is link from another table. Now my keyword search form is only PatientFullname. And I created the code for the search button as below:
 
Private Sub cmdSearch_Click()
Dim Search As String
Dim fullname As String
If IsNull(Me.txtSearchPatientsNames) Or Me.txtSearchPatientsNames = “” Then
        MsgBox “Enter Keyword Please.”, vbOKOnly, “Keyword Needed”
        Me.txtSearchPatientsNames.BackColor = vbYellow
        Me.txtSearchPatientsNames.SetFocus
Else
        fullname = Me.txtSearchPatientsNames
        Search = “SELECT * FROM tblPatientsServices WHERE ((PnameFullname LIKE “”*” & fullname & “*””))”
        Me.RecordSource = Search
         Me.txtSearchPatientsNames.BackColor = vbWhite
End If
End Sub
 
Now I want to have more code to Search data from multiple fields using operator “OR” to search PatientVillage field with the same keyword search place of PatientFullname. Because this data type field is number and link from another table.
So, Could you please to show the code how to do that?
Thank you very much, I hope so maybe many people wait your reply
 Wish you have a good luck

1 Answers
TGoldenEye Staff answered 3 months ago

The easy way is to create a query from two tables and insert all fields from table: PatientsID, PatientFullname, PatientDOB, PJob accept field PatientVillage. Then insert a Villagename field from a village table. You will need to search from a query like:

strCriteria = “(PatientFullname LIKE “”*” & fullname & “*””)”
strCriteria = strCriteria & “OR (VillageName LIKE “”*” & Me.txtVillageName & “*””)”
Search = “SELECT * FROM QueryName WHERE (” & strCriteria & “)”

More information on creating a query from multiple tables here:

How to Create Query from Multiple Tables

Your Answer