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:
- Create a new workbook.
- On the Data menu, point to Get External Data, and click Create New
Query.
- 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.
- 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.
- 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.
- To view the criteria pane, click Criteria on the View menu.
- Click Add Criteria on the Criteria menu.
- 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.
- 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.
- Click OK, and then click Close.
- On the File menu, click "Return Data to Microsoft Excel."
- In Microsoft Excel, click OK.
- 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 proper argument msquery
Keywords : kbtool kbualink97 xlquery
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowto
|