ACC2000: How to Return Case-Sensitive Matches in Queries The information in this article applies to: Microsoft Access 2000This article was previously published under Q209674 Advanced: Requires expert coding, interoperability, and multiuser skills. This article applies only to a Microsoft Access database (.mdb). SUMMARY You can use the Asc() function or the StrComp() function to have Microsoft Access differentiate between case-sensitive text strings. When you use the Asc() function in a select query, Access can locate an exact, case-sensitive match using the first letter of a text string. The StrComp() function can perform a case-sensitive match for the whole string. Both of these functions can be useful in checking for names in a database that are supposed to be uppercase or proper case, but which have been entered incorrectly. MORE INFORMATION To find all the records in a field that contain lowercase text rather than uppercase text for the first character, use either of the following methods. Method 1: Find Text With an Initial Lowercase Letter Using the Asc() Function Start Microsoft Access, and then create a new blank database. Create a new table with the following field: Field Name: FirstName Data Type: Text Field Size: 50Field Name: LastName Data Type: Text Field Size: 50 Save the table as tblNameList. When you are prompted to create a key field, click No. Add the following records to the tblNameList table: FirstName LastName --------- ---------- Andrew Maclean Nancy Macleod Zareda van Helsing andrew MacLean nancy MacLeod zareda Van Helsing
Create a new query based on the tblNameList table, and then enter the following data in the query grid: Field: FirstName Table: tblNameList Show: TrueField: LastName Table: tblNameList Show: TrueField: Asc([FirstName]) Show: False Criteria: >=Asc("a") And <=Asc("z") Run the query. Note that you have selected only the records that contain first names with an initial lowercase letter. Method 2: Find Text With a Lowercase Letter in Any Position Using the StrComp() Function If you have not already done so, create the test database using steps 1 through 3 in Method 1. Create a new query based on the tblNameList table, and then enter the following data in the query grid: Field: StrComp([FirstName],"a",0) Show: False Criteria: 1Field: FirstName Table: tblNameList Show: TrueField: LastName Table: tblNameList Show: True Run the query. Note that the query returns the same results as in Method 1. Open the query in Design View, and then change the Field parameters in the first column of the query grid to: Field: StrComp([LastName],"Mack",0) Run the query. Note that the query returns "Maclean" and "Macleod," but not "MacLean" and "MacLeod." The records for "van Helsing" are also selected because the criteria you entered for the StrComp() function returns any binary values that are greater than the search criteria "Mack." REFERENCES For more information about the Asc() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type asc function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.For more information about the StrComp() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type strcomp function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.Last Reviewed: 3/10/2003 Keywords: kbhowto kbinfo kbusage KB209674
对每一个字符，if >=Asc("a") And <=Asc("z") ,then 小写 if >=Asc("A") And <=Asc("Z") ,then 大写
The information in this article applies to:
Microsoft Access 2000This article was previously published under Q209674
Advanced: Requires expert coding, interoperability, and multiuser skills. This article applies only to a Microsoft Access database (.mdb).
SUMMARY
You can use the Asc() function or the StrComp() function to have Microsoft Access differentiate between case-sensitive text strings. When you use the Asc() function in a select query, Access can locate an exact, case-sensitive match using the first letter of a text string. The StrComp() function can perform a case-sensitive match for the whole string. Both of these functions can be useful in checking for names in a database that are supposed to be uppercase or proper case, but which have been entered incorrectly.
MORE INFORMATION
To find all the records in a field that contain lowercase text rather than uppercase text for the first character, use either of the following methods.
Method 1: Find Text With an Initial Lowercase Letter Using the Asc() Function
Start Microsoft Access, and then create a new blank database.
Create a new table with the following field:
Field Name: FirstName
Data Type: Text
Field Size: 50Field Name: LastName
Data Type: Text
Field Size: 50 Save the table as tblNameList. When you are prompted to create a key field, click No.
Add the following records to the tblNameList table: FirstName LastName
--------- ----------
Andrew Maclean
Nancy Macleod
Zareda van Helsing
andrew MacLean
nancy MacLeod
zareda Van Helsing
Create a new query based on the tblNameList table, and then enter the following data in the query grid:
Field: FirstName
Table: tblNameList
Show: TrueField: LastName
Table: tblNameList
Show: TrueField: Asc([FirstName])
Show: False
Criteria: >=Asc("a") And <=Asc("z") Run the query. Note that you have selected only the records that contain first names with an initial lowercase letter.
Method 2: Find Text With a Lowercase Letter in Any Position Using the StrComp() Function
If you have not already done so, create the test database using steps 1 through 3 in Method 1.
Create a new query based on the tblNameList table, and then enter the following data in the query grid:
Field: StrComp([FirstName],"a",0)
Show: False
Criteria: 1Field: FirstName
Table: tblNameList
Show: TrueField: LastName
Table: tblNameList
Show: True
Run the query. Note that the query returns the same results as in Method 1.
Open the query in Design View, and then change the Field parameters in the first column of the query grid to:
Field: StrComp([LastName],"Mack",0) Run the query. Note that the query returns "Maclean" and "Macleod," but not "MacLean" and "MacLeod." The records for "van Helsing" are also selected because the criteria you entered for the StrComp() function returns any binary values that are greater than the search criteria "Mack."
REFERENCES
For more information about the Asc() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type asc function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.For more information about the StrComp() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type strcomp function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.Last Reviewed: 3/10/2003
Keywords: kbhowto kbinfo kbusage KB209674
if >=Asc("A") And <=Asc("Z") ,then 大写