Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter for Upper/Lower case only (Access 97 SR-2)

    I have a field which contains the letter Y for various records - some are upper case and some are lower case (there is a reason for them being like that) and I need to be able to filter for just the upper case Y's or vice versa.

    I'm sure there's an easy way of doing it but I can't remember how... I've tried using the following criteria for the upper case Y's:

    Like Chr(89)

    But this doesn't work... can anybody please help me with this?

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for Upper/Lower case only (Access 97 SR-2)

    In the design of the query, add a column

    CharCode: Asc([FieldName])

    where FieldName must be replaced by the actual name of the field you want to filter on. This column will contain 89 for Y and 121 for y. You can filter on this calculated column.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    United Kingdom
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for Upper/Lower case only (Access 97 SR-2)

    Perfect! Thanks for that Hans =)

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

    Re: Filter for Upper/Lower case only (Access 97 SR-2)

    For anyone interested, another option for case-sensitive text comparison where field's text may be more than one character in length is to use InStr function with option to perform binary comparison. Example of query criteria:
    <pre>WHERE (((InStr(1,[FIELDNAME],"Text",0))=>0))</pre>

    where FIELDNAME is name of field and "Text" is comparison text (one or more characters). You'd add a column to the query for this expression. This is equivalent of using Like operator in expression:
    <pre>Like *Text*</pre>

    only the InStr expression is case-sensitive. The InStr compare argument of 0 (zero) tells function to perform a binary (case-sensitive) comparison. Since Access by default is not case-sensitive I would try to avoid using case-sensitive data where possible in first place.

Posting Permissions

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