XL97: Parameter Query Is Case Sensitive

ID: Q160487

The information in this article applies to:

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.

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

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

REFERENCES

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

   parameter queries

and then double-click the selected text to go to the "Create a query that prompts for criteria" topic.

Additional query words: XL97 8.0 8.00 lower case upper case proper argument msquery

Keywords          : kbtool kbualink97 xlquery 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: November 1, 1998