Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Uppercase (2000 / xp)

    I have a field in my table that stores string/text values. Some of these values are uppercase and some are mixed case. I wanted to build a query that just gives me those values that are in upper case. Does any one know how to do this? Any help would be much appreciated.

    Thanks

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Uppercase (2000 / xp)

    Access in general is not case-sensitive so there is no built-in way to distinguish upper and lower case in a field. You'd have to use a user-defined function for this. Example:

    Public Function IsUpperCase(ByRef strTxt As String) As Boolean

    Dim n As Long
    Dim b() As Byte

    ' Convert from Unicode to byte array of ANSI character codes:
    b = StrConv(strTxt, vbFromUnicode)

    IsUpperCase = True

    For n = 0 To UBound([img]/forums/images/smilies/cool.gif[/img]
    Select Case b(n)
    Case 97 To 122
    IsUpperCase = False
    Exit For
    End Select
    Next

    Erase b

    End Function

    This function tests the ASCII code for each character in a text string till it finds a lower case letter (ASCII 97 to 122), which will return False. If no lower-case character found, function returns True. Function can be used in a query to identify a field with all upper-case characters (numbers & symbols are disregarded). Example:

    SELECT Table1.Field1, IsUpperCase([Field1]) AS [Upper Case]
    FROM Table1;

    See sample results in attached pic. Example that will return only records where Field1 is all upper case:

    SELECT Table1.Field1
    FROM Table1
    WHERE (((IsUpperCase([Field1]))=True))

    HTH
    Attached Images Attached Images

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Uppercase (2000 / xp)

    If you only want to *change* those uppercase values to mixed case, it would be faster to simply do an update query using the StrConv function and not worry about whether they're already proper cased.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uppercase (2000 / xp)

    Mark D,

    Thanks for that. I thought there might be a way to query for upper case using something similar to an input mask, but obviously not. A user-defined function it is then!

    Amanda

  5. #5
    New Lounger
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uppercase (2000 / xp)

    Charlotte,

    I don't want to change the values, just query them. Thanks though

    Amanda

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Uppercase (2000 / xp)

    One note, something I noticed after posting reply, when testing function if you get what appear to be bogus results, that is, the text in field appears to be all upper case, but function returns False, check the field properties in Table design view, there may be an input mask or format property defined for field that forces the text to appear all upper case (using greater than (>) symbol), but the actual data contains upper & lower case characters. The function evaluates the data as actually stored in table, not what is displayed by an input mask or format property. The same applies to format settings or input masks defined at the query, form, or report level.

    HTH

  7. #7
    New Lounger
    Join Date
    Sep 2003
    Location
    London, England
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Uppercase (2000 / xp)

    Mark,

    I didn't have an input mask, but definately worth noting. Thanks

    Amanda

Posting Permissions

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