XL98: How to Create Parameter Queries

ID: Q180184

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition

SUMMARY

A parameter query is a kind of query that prompts you to specify values that are used to select the records for the result set. This allows the same query to be used to retrieve different results. You can create a custom prompt that describes the requested parameter and that is displayed when the query is run.

MORE INFORMATION

A result set is a set of records that results when you run a query. You can see the result set of a query in Microsoft Query or you can return a result set to a Microsoft Excel worksheet for further analysis. Microsoft Query displays the result set in row-and-column format in the Data pane.

The criteria is one or more conditions you specify to limit which records are included in the result set of a query.

Example

The following example, using the sample dBASE file Order.dbf, creates a parameter query that selects records by city. When you run the query, a prompt appears and displays "Type the name of the city." The result set returns only the records for the city that is entered by the user.

NOTE: a parameter query is case-sensitive.

To use this example, follow these steps:

On the Apple Menu, point to Control Panels and click ODBC Setup PPC. In the User DSN tab, click Add.

In the Create New Data Source dialog box, click Microsoft 3.01 dBASE PPC and click Finish.

In the General tab, type TestDBF for the Data Source Name. Click Select Directory and select the Sample Databases folder located under the Microsoft Office 98:Sample Files folder.

In the ODBC Data Source Administrator, click OK.

Start Microsoft Excel. Point to Get External Data on the Data menu and click Create New Query.

In the Choose Data Source dialog box, click TestDBF*. Clear the Use The Query Wizard check box. Click OK.

In the Add tables dialog box, click Customer.dbf. Click Open and click Cancel.

The Customer table should be added to Microsoft Query.

In Microsoft Query, double-click the asterisk in Customer table to add all the records to the Data Pane.

On the View menu, click Criteria. Click City in the Criteria Field. In the Value field type the following:
["Enter a city."]

If the Enter Parameter Value dialog box appears, type Seattle . On the File menu, click "Return Data to Microsoft Excel."

In the "Returning External Data to Microsoft Excel" dialog box, click Existing worksheet and =$a$1. Click OK.

In the Enter Parameter Value dialog box, type Seattle .

NOTE: This dialog box is case sensitive.

To change parameter value after data is returned to the worksheet, click Refresh Data on the Data menu.

Using the Like Operator

To return all the data in a parameter query or partially matching records in a field, ensure that when you create the parameter, the leading parameter bracket ([) is preceded by the operator LIKE. LIKE is used in conjunction with the % wildcard. The % wildcard is similar to the * wildcard in MS-DOS, which returns all characters.

For example, using the example above, change the parameter in step 9 from the following

["Enter a city."]

to the following

Like ["Enter a city."]

To use this parameter and return all records that begin with the letter "S," type S% in the Enter Parameter Value dialog box. To return all records, enter the % by itself.

For additional information, please see the following articles in the Microsoft Knowledge Base:

Q188224 XL98: How to Use Wildcards in Parameter Queries

Q179700 XL98: "Out of Range" Error Running Parameter Query in MS Query

Additional query words: OFF98 xl98 msquery

Keywords : xlquery
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto