XL97: Parameter Query Is 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.

The scalar function UCASE is used to convert values to upper case. When you use it for this purpose, the function converts both the field (or expression) and the parameter value to upper case. This allows you to type a value without regard to case.

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). 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.

When you refresh the query, you do not have to be concerned with the case of the value that you type for the parameter.