Download Sample Access File (Version 2010 and up):
Search any field up to 3 Keywords
This is a simple search form that can search up to three (3) keywords at once. 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 record source of subform to the SQL of search function. The search function will search all fields on the current form. The use can enter keywords in three text box provided. Also the user can use the operator “AND” and “OR” to connect between the three keywords. The default operators are set to “AND” and “AND.” The user also is able to a print report from the result of searching.
The function is looking for the keywords in all fields by using the For… Loop as shown in the example below
Highlight functions in this file include:
#1. One Keyword Search
The search form will open and ready to enter the first keyword. The text boxes for the second and third keyword will be disabled after form is loaded. The first operator and the second textbox will be enabled after the first keyword is entered into the first textbox. For example, the keyword “ca” is entered then click Search icon. The result will display only the records that match with “ca” in any field. The keyword “ca” can be found under the State field. There are 14 records found for keyword “ca” as shown in the picture below.
#2. Search 2 Keywords with AND operator
There are 14 records displayed after we search one keyword for “ca” on #1 above. We enter the second keyword “fresno” on the second textbox. The search function is looking for the records that have both keywords “ca” and “fresno” on any field in same record. There are 5 records found per picture below.
#3. Search 2 Keywords with OR operator
We change the operator from AND and OR with same keywords “ca” and “fresno”. There are 14 records found with keyword “ca” and 1 record for keyword “fresno” that is not in CA state (in TX State). The total records are 15 as the result of using OR operator.
#4. Search 3 Keywords with AND and AND operator
The function is looking for the records that have information in any field that matches all 3 keywords in three textboxes. For example, 3 keywords are entered as ca, fresno, and 559. The function will create a temp query to store the result from searching for the first two keywords (ca, fresno) then will use the third keyword (559) to search for the record in the temp query. There will be only 4 records that meet the criteria as shown below.
#5. Search 3 Keywords with AND and OR operator
There are 5 records that are the result from searching for keyword “ca” and “fresno” as shown on #2. The third keyword “559” is connected with the “OR” operator. There are many records that meet 559, but only 4 more records have no duplicated record from the result of first two keywords. That means the total record of searching is the combination of the result of the first two keywords (ca, fresno) and the result of searching for 559 keyword. The duplicated records will be displayed as one record. The total search result will be 9 as shown below.
#6. Search 3 Keywords with OR and OR operator
This function will be looking for the records that meet any one of three keywords in any field. Some records may contain more than one keyword. For example, 3 keywords are entered as “Maple”, “Clovis”, and “non”. There are 7 records that contain these 3 keywords. Each keyword does not have to be in one record as shown in the picture below.
#7. Clear Keywords
This form provides a Clear button to clear all 3 keywords and change the operators to AND. It also clear all the records from the datasheet of this split form.
#8. Show All
This form also provides a Show All button to display all records. It also clear all 3 keywords and change the operators to AND.
#9. 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.
Example #1 -There are 4 records as the result of searching for 3 keywords below. The same records will display on the report as shown in the picture below.
Preview Report:
Example #2 – There are 9 records from searching for 3 keywords below. The same records will be displayed on the report as shown below.
Preview Report:
Download file contains:
3 tables, 2 queries, 1 Forms and 1 report
