Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: Find upper case text in text field using sql query

    I have an MSDE application w/ a MS Access 2000 back end. There is a text field that contains text strings some of which are state abbreviations, i.e., LA, MS, TX, etc. I am trying to write a sql select statement to find only those records w/ such state entries. Due to Access/sql case insensitivity, my query finds them, but also all other records that have lower case combos of these letters as well. Can anyone clue me in as to the Access/sql syntax to isolate the upper case letters? TIA--Ed

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'm not 100% sure what you need.
    If you need all records which begin with capital letter use:
    Code:
    SELECT *
    FROM t
    WHERE asc(Mid([text_column],1,1)) Between 65 and 90;
    if you need all records where first 2 letters are capital use:
    Code:
    SELECT *
    FROM t
    WHERE asc(Mid([text_column],1,1)) Between 65 and 90
    AND asc(Mid([text_column],2,1)) Between 65 and 90;

  3. #3
    Join Date
    Dec 2005
    Posts
    2

    Find Upper Case Text in Text Field using sql query

    Let me try to be more explicit. In some text fields of a VB6 front end/ MSDE MS Access dba application there is a variety of data, e.g., say

    Voucher given $65. Client home of origin New Orleans LA FEMA#3665677

    There are thousands of records only a few hundred of which contain the "LA". What I'm trying to select are only those records that have the two digit capitals in this text field.

    My attempts so far do capture the above record, but also a bazillion others with "Lane" or "late" or "Labadie," etc., and not "LA". So I'm try to restrict the query to only selecting the "LA" records.

    Thanks for taking a look. Any suggestions appreciated.
    Last edited by StL MacMan; 12-02-05 at 14:33.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ehhhh... you know what the ASC() function does right?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2005
    Location
    Ontario, Canada
    Posts
    4
    In your criteria try something like this.
    Like "la" AND NOT Like "laa*"
    I tried a few with lat, lad, late and comes back with la.
    HTH

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Another thought, do the first two letters HAVE to be capitol or can you search for two letters and a space?

    If so, you could use something like this:

    SELECT str_field
    FROM yourTable
    WHERE LEFT(str_field, 3) LIKE '[A-Z][A-Z][ ]'
    Last edited by Teddy; 12-02-05 at 15:18.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •