Search Multiple Fields

Download Sample Access File: Search Multiple Fields

Create Search Form for multiple items in multiple fields using different search methods. There are five forms in this sample file. Each form shows different type of search function or filtering multiple data items by using text box, combo box and/or list box. This sample file also shows how the text boxes, combo boxes and list box works together.

Highlight functions or features in this sample file include:

Form 1 – Search Multiple Fields

It is considered a complete search form since it is able to search on multiple fields with different methods. It uses a combination of text box, list box and combo box. There are different types of input data for searching listed below:

mutiple fields - Copy

#1 You can search by key words on Customer Name, City, and phone fields. The example below is searching for “los” on City field and “Male” on Gender field. The result of male gender and live in Los Angeles city are displayed below.

search two fields - Copy

#2 Search multiple number or zip codes in one text box by using comma (,) as a separator. The result will display all records with zip code that match the input zip codes on Zip Code field.

search zipcode#3 Search the date range on the Date field by entering the starting date and the ending date. On the example, entering date from 1/1/15 to 10/1/15 on the DOB field it will display all customers that born from 1/1/15 – 10/1/15.

search DOB - Copy

#4 Search the number range on the number field by entering the starting number and the ending number. For example, entering number from 2 to number 10 on the Age field it will display all customers that have age between 2 to 10.

search age - Copy

#5 This form also provides a Preview Report button to print the result from searching. It also provides a Clear All button to clear all inputs and Clear Select button to clear the select check box on the list.

select to print

#6 Print certain record after search. If you want to print only certain record from the list you can check box on the select column then click Preview Report button. If there is no check on any record it will print all records on the list. For example, check on record of Sven Mortensen and Roland Wacker then click Preview Report it will display only two records of Sven Mortensen and Roland Wacker as shown below:

print selected cust

Form 2 – Search Three Fields Using a Combo Box and List Box

This part is combining a combo box or list box for searching or filtering on multiple fields. There are three fields used in this sample file. The result of searching will display on this split form if all criteria matching on three fields. If there is no item selected on the combo box or list box it will be no criteria for that field and will display all data for that field.

#1 Search or filter on multiple fields in combination of selecting a single item from a combo box and multiple items on a list box. Example below:
Gender = Male
State = CA, Co or NV
Customer Type = Individual

three combined

#2 This form also provides a button to preview a report or list of customer that is a result from the search or filtered.

three combined report

#3 Clear Combo box and List box. A Clear List button will clear all combo box and list box for Gender, State and Customer Field. Then it will set a filter to false and display all data on this split form.

three combined clear

 

Form 3 – Search Form for Multiple Items Using a Combo Box and List Box

This part is a combination of a combo box or list box for searching or filtering. Users have an option to select a single item by using a combo box to filter or can able to select multiple items by using a list box.

combo and list box

#1 Search multiple items by selecting a –Select Multiple- item from a combo box or by clicking on the (+) button to display a list box. Then user can select multiple items from the list box.
For Example:
Customer Type = Government or Individual

combo and list box multiple

#2 The combo box will display the item as –Select Multiple- is selected after clicking (-) button to hide the list box. The value of multiple selected items is stored in a temp variable and references to the Customer_Type_ID of –Select Multiple- item.

combo and list box multiple result

#3 Clear Combo box and List box. A Clear List button is provided to clear the selected item from the combo box and list box. It will set a filter to false and display all data from a Customer table on this split form.

clear list

Form 4 – Search Using a List Box for a Number or Integer

After the item is selected on the list box it will call a search function/sub to filter the customer type and display on the datasheet of a split form.

integer list box

A Row Source of the list box comes from a table Customer Type. Two fields are listed on the query of row source. The list box references to the first column on the query which is a Customer_Type_ID field. This field has a data type as integer or number. The search function is looking for the number of Customer type ID on the list box. So the VB code is different from the previous search for State list box. You will find it in this form.

integer cust

This form also provides a button to preview a report or list of customer that is a result from the search or filtered.

report for integer list box

Form 5 – Search Using a List Box for Text or String

After the item is selected on the list box it will call a search function to filter the states and display on the datasheet of a split form. The state of CO, NV and TX is selected on this example.

string list box

A Row Source of the list box comes from a group of data from a State field of Customer table. A list of data or State will depend on the input data on this table. This field has a data type as text or string.

String state query

This form also provides a button to preview a report or list of customer that is a result from the search or filtered.

report for string list box

Modules

There are two modules that contain some public functions which are used in the VB in forms.

For example, public function ClearList() is used to clear the items in list box. This function is called when the Clear List button is clicked.

module function

 

Download file contains:
2 tables, 1 Query, 5 Forms, 1 Report and 2 Modules

table list