XL97: Parameter Query Is Case Sensitive

• Microsoft Excel 97 for Windows

SUMMARY

The value you type for a parameter query that you create with Microsoft Query is case sensitive. When you type a value in a case that does not match the value of the field in the external database, the query may not return data.

This article describes how to create a parameter query that is not case sensitive.

MORE INFORMATION

To create a parameter query that is not case sensitive, do the following:

• Use the scalar function UCASE for the field (or expression) in the criteria.

      -and-
  

• Use the scalar function UCASE for the parameter.

Example

WARNING: Your use or modification of the SQL statement provided in this article is at your own risk. Microsoft provides this SQL statement "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Microsoft does not support modifications of the SQL statement to suit specific customer requirements.

This example creates a parameter query from the sample dBASE IV file Employee.dbf that is included with Microsoft Excel 97. The case-insensitive parameter uses the Last_Name field in the table. (The default location for the sample file is C:\Program Files\Microsoft Office\Office\Employee.dbf.) To demonstrate how to create a case-insensitive parameter query, follow these steps:

1. Create a new workbook.

2. On the Data menu, point to Get External Data, and click Create New Query.

3. When you are prompted to select a database, select a data source that uses the dBASE driver, clear the "Use the Query Wizard to create/edit queries" check box, and then click OK.

4. When you are prompted to add a table to the query, select C:\Program Files\Microsoft Office\Office\Employee.dbf, click Add, and then Click Close.

5. In the Query window, double-click the asterisk (*) in the Employee table pane.

   This step adds all of the records from the Employee table to the data pane.

6. To view the criteria pane, click Criteria on the View menu.

7. Click Add Criteria on the Criteria menu.

8. In the Field box, type "UCASE(Last_Name)" (without the quotation marks). In the Operator box, click "equals." In the Value box, type the following:

   UCASE([Enter the employee's last name:])

   and then click Add.

9. When you are prompted to type a value for the parameter, type one of the values in the Last_Name field of the Employee table. When you type this value, use a different case.

10. Click OK, and then click Close.

11. On the File menu, click "Return Data to Microsoft Excel."

12. In Microsoft Excel, click OK.

13. When you are prompted to type a value for the parameter, type one of the values in the Last_Name field of the Employee table and use a different case. Click OK.

REFERENCES

For more information about creating a parameter query, click the Index tab in Microsoft Excel Help, type the following text

   parameter queries