• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

Limit Query Results

I have a file of names where some are properly cased and some are all lowercase and some are all uppercase.  I am interested in only viewing the names in the table that are all uppercase or all lowercase.  How do I achieve this through MS Access?
1 Solution
do you mean upper or lower case a to z characters?
if so you would need to loop through that table and check every individual character (loop) in that field in usng the asc function (ascii function):
asc("A") returns 65
asc("Z ") returns 90

asc("a") returns 97
asc("Z ") returns 122

so every character needs to be between 65 to 90 OR between 97 to 122, mutually exclusive
if you get a field with a mix of characters between 65 to 90 ANd between 97 to 122 the you its a mix of lower and upper case
This query will find only rows that are entirely uppercase or entirely lowercase:

SELECT Table1.Field1, IIf(StrComp(LCase([field1]),[Field1],0)=0 Or StrComp(UCase([field1]),[Field1],0)=0,True,False) AS CheckString
FROM Table1
WHERE (((IIf(StrComp(LCase([field1]),[Field1],0)=0 Or StrComp(UCase([field1]),[Field1],0)=0,True,False))=True));

Table1.Field1 is the field you are evaluating.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now