FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Using FrontPage to Search Multiple Fields in the Same Table

This tutorial is part of a larger discussion on creating search forms for your FrontPage Database website. Click here to read the full tutorial.

The process involves several steps

1. Searching begins with website design

2. Create a Progressive Search Form

3. Add an All Values option to your Search Form

4. Using Drop-down Menus with Multiple Select

5. Add a Text Search Field Using the DRW

6. Search Multiple Fields in the Same Table

7. Search Multiple Fields or Tables in the Same Database

For many search forms, it is important be able to search several fields in a table simultaneously. You want your users to be able to type in a search term and have it return the database record regardless of whether they enter a company name OR a contact name. 

As an example, in MountainNature.com, I had three tables that included species data, one each for plants, animals and birds. Each of the tables contained fields for Common Name and Latin Name. I wanted to create a search form that would allow a user to put in either the common OR the Latin name and still return a result.

Surprisingly, after struggling for hours, I stumbled on a simple custom sql query that achieves exactly what I needed. In step 2 of the Database Results Wizard, I used the following sql:

SELECT CommonName, IndexName, LatinName
FROM AnimalIndexName
WHERE CommonName LIKE '::SearchTerm::'
OR LatinName LIKE '::SearchTerm::'
ORDER BY IndexName;

Let's create an example using the Northwind Database

In this tutorial, we'll create a similar example using the Northwind database. In this case, using the Customers table, we'll create a form that will allow you to find records regardless of whether they enter a company name OR a contact name.

On a blank page, select Insert Database Results. Select the Northwind database connection and click the Next button to move to Step 2. This is where we'll enter the Custom SQL code to make this custom search form work.

Type the following into the custom sql:

SELECT CompanyName, ContactName
FROM Customers
WHERE CompanyName LIKE '::SearchTerm::'
OR ContactName LIKE '::SearchTerm::'
ORDER BY CompanyName;

This tells the Database Results Wizard (DRW) to find records by comparing the field SearchTerm against either the CompanyName OR the ContactName fields. Click OK to close the Custom Query window and then Next to move to Step 3.

Click the More Options button and type "Type a Search Term in the Form Provided Above" into the Message to Display if No Records Returned field. This will prompt the user to type a search term when the form is first opened.

Click next to step through the DRW, selecting Table - one record per line in Step 4 and finally Add Search Form in Step 5. Your Database Results Wizard should resemble the following:

Don't forget to use wild cards to make your search more effective. In these search forms, the percent symbol (%) acts as a wild card.

1. Find names that begin with a letter or phrase by placing the percent sign after the letter or phrase (phrase%).
2. Find names or companies that contain a phrase by enclosing the phrase within percent signs (%the%).
3. Find names or companies that end with a letter or phrase by entering %phrase into the search term.

You can give it a try here:

Don't forget that you are not limited to using the built-in search form as part of the DRW.

This versatile technique helps you smash through the limits placed upon you by most simple search forms. Now, you can take your search forms to the next level.

In our next tutorial, I'll show you how to use this technique to search for a term within one or more fields in two different tables. Click here to read it now