Download Sample Access File: Search by Multiple Keywords
(For Version 2010 and up)
This search form is able to search for multiple keywords from one input textbox. The search form is created under a Split Form format which has the detail section displayed similarly to the datasheet subform. You can use the function on subform as well by changing the Recordsource of subform to the SQL of search function. The user can enter multiple keywords in the provided text box to search for those keywords. All search forms come with the Search button, Clear, Show All, Print Preview the result, and Close form buttons. The user is also able to print a report from the result of searching.
There are four examples of search form in this download file:
- Search Multiple Words on One (1) Field
- Search Multiple Words on Three (3) Fields
- Search Multiple Words on All Fields
- Search Multiple Words on One Field with AND Operator
#1 Search Multiple Words on One (1) Field
The search form will open and ready to enter keywords. This search form will search multiple keywords on only one field that is assigned to a “CustomerName” field.
Example #1 – Enter keyword “john” and click Search icon or hit Enter key. The result will display only the records that contain “john” in the CustomerName field. There are 3 records found as shown in the picture below.
Example #2 – Enter two keywords “john jose” separated by space. The logic of search is john OR jose. The OR operator is used to find the customer name. The result will display only the records that contains “john or jose” in the CustomerName field. There are 4 records found.
Example #3 – Enter three keywords “john jose paul” separated by space. The logic of search is john OR jose OR paul. The result will display only the records that contains “john or jose or paul” in the CustomerName field. There are 7 customers found as shown below.
Example #4 – Enter five keywords “john jose paul bob nancy” separated by space. The logic is searching for customer name that contains john or jose or paul or bob or nancy. The result will be 9 customers found.
#2 Search Multiple Words on Three (3) Fields
This search form will search in three fields: CustomerName, Address, and City. You can set more than three fields as you want by updating the VB code in the Search function.
Example #1 – Enter keyword “Clovis” and click Search icon or hit Enter key. The result will display only the records that contain “Clovis” in the CustomerName, Address, and City field. There are 2 records that found in the City field as shown below.
Example #2 – Enter keyword “Main”. There are 2 records that found in the Address field as shown below.
Example #3 – Enter keywords “Main Clovis John”. There are 7 records that found from all three fields: CustomerName, Address, and City as shown below.
Example #4 – Enter keywords “Main Clovis John Fresno”. There are 12 records that found from all three fields: CustomerName, Address, and City as shown below.
#3 Search Multiple Words on All Fields
This search form will search multiple keywords from all fields in this form. You just type all keywords with space between keyword and hit Enter or click Search button.
Example #1 – Enter keyword “government NV” and click Search icon or hit Enter key. There are 2 records from the result of searching for “government” word found under the Customer_Type field. There are another 3 records found for “NV” keyword under the State field. There are 5 records found in total.
Example #2 – Enter keyword “business NV 937”. There are 7 records found from searching for “business” keyword under the Customer_Type field. There are 3 records found for “NV” keyword under the State field. And there are 5 records found under the Zip field. There are total of 15 records found.
#4 Search Multiple Words on One Field using AND Operator
This search form will search multiple keywords on only field “CustomerName”.
Example #1 – Enter keyword “paul” and click Search icon or hit Enter key. There are 3 customers that have their name contain word “paul” with different last name.
Example #2 – Enter keyword “paul vang”. Now we enter a keyword “vang” after “paul” to narrow down the search criteria. There is only one customer found as shown below.
#5 Multiple users can search same time
The result of searching will be added to the TempSearch table. It also will add the UserLogin name to each record of search result. Every time the user performs searching, it will delete only record or data that produced by the user from the TempSearch table. For example below, there are 3 customers found when I perform searching for john. My login name “Tewan” is also stamped in the TempSearch table for those records. It will be deleted as well before performing a new search. The system will not delete the search result produced by another user.
#6 Print Preview Report
This form also provides a Preview Report button. The report will display the same records as the result of searching on the form. There are 3 records as the result of searching for “paul” keywords below. The same records will display on the report as shown in the picture below.
Download file contains:
3 tables, 2 queries, 5 Forms, 1 report, and 1 module
