Australian PC World - Search an Access database for two words at once

Search an Access database for two words at once

Tip
When you want to search an Access database for all records that contain a specific word -- say, pepper -- the Edit--Find command is fine. But if you want records that contain both pepper and fiery, you need a query. Here we create one that lets us search for two words. We'll attach the query to a search form, where you enter the words, and a display form to show the records.
1. To create the query, open the sample Northwind database, click the Queries tab, then click New. Leave Design View highlighted and click OK. In the Show Table dialogue box, double-click Products and click Close. In the Query window, click and drag the * under Products in the top half of the window to the Field row of the first column in the query grid. Click and drag the ProductName field to the second column of the grid and uncheck its box in the Show row. In the Criteria row for ProductName, type:
Like "*" & [Forms]![Find Products]![Word 1] & "*" And Like "*" & [Forms]![Find Products]![Word 2] & "*"
Save the query as Find Products.
2. Now we create the display form. You can copy an existing form or design a new one. Here, we'll revise the Product List form. In the Database window, click the Forms tab, highlight Product List, and click Design. Right-click the title bar of the form and click Properties. Click the Data tab in the resulting dialogue box to show the Record Source property. From the Record Source menu, select the Find Products query we created earlier. Close the form and save your changes.
3. Now you're ready to create the search form. In the Database window, click the Forms tab, then click New. Leave Design View highlighted and click OK.
Next, click the Text Box tool in the Toolbox and use it to add a text object to the form. Right-click the new object, choose Properties, click the Other tab, and change the Name property to Word 1. Right-click the label box to the left of the object, choose Properties, click All, and change the Caption property to First word. Click the text box again and choose Edit--Duplicate. Change the Name property to Word 2 and the Caption property to Second word.
In the Toolbox, click Command, then draw a button under the text objects. In the first step of the Command Button Wizard, click Form Operations in the Categories list, and click Open Form in the Actions list. Click Next, select Product List for the form you want the button to open, and click Next. For the third step, click Next. In the fourth step, click Text, enter Find Products for the button's label, and click Next. In the final step, enter Find Products for the button's name, and click Finish. Save the form as Find Products.
4. To use your new search form, click the Forms tab in the Database window, select Find Products, and click Open. Type the words you want to search for and click Find Products. The matching records will appear in the form you created in step 2.
- Celeste Robinson

Category: Data management
Issue: Nov 1996
Pages: 161