Limit Query Results

Posted on 2007-10-18
Last Modified: 2013-11-27
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?
Question by:Millie1919
    LVL 19

    Expert Comment

    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
    LVL 2

    Accepted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now