XL2000: Parameter Query Is Case-Sensitive (211974)

MORE INFORMATION

To create a parameter query that is not case-sensitive, make sure that both of the following conditions are true:

• The scalar function UCASE for the field (or expression) is used in the criteria.
  
  -and-
• The scalar function UCASE is used for the parameter.

The scalar function UCASE is used to convert values to uppercase. When you use it for this purpose, the function converts both the field (or expression) and the parameter value to uppercase. 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 2000. 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 create a case-insensitive parameter query, follow these steps:

1. In Microsoft Excel 2000, create a new workbook.
2. On the Data menu, point to Get External Data, and then click New Database Query.
3. When you are prompted to select a database, select dBASE Files*, click to 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, navigate to C:\Program Files\Microsoft Office\Office\1033\Employee.dbf, click Add, and then click Close.
5. In the Query window, double-click the asterisk (*) in the Employee pane.
   
   This step adds all of the records from the Employee table to the data pane.
6. Click Criteria on the View menu, and then click Add Criteria on the Criteria menu.
7. In the Add Criteria dialog box, follow these steps:
   1. In the Field box, type UCASE(Last_Name).
   2. In the Operator box, click equals.
   3. In the Value box, type UCASE([Enter the employee's last name:]).
   4. Click Add.
8. 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.
9. Click OK, and then click Close.
10. On the File menu, click Return Data to Microsoft Excel.
11. In Excel, 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.