Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Differentiate between Upper/Lower Case in DLookUp (Access 2000 SR1)

    I have an

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Differentiate between Upper/Lower Case in DLookUp (Access 2000 SR1)

    This is not easy. You will have to use the StrComp function as part of your WHERE clause, using a binary compare.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Differentiate between Upper/Lower Case in DLookUp (Access 2000 SR1)

    As recommended you can use StrComp function with DLookup using syntax like this:

    <pre>=DLookup ("Item Description", "Items", "StrComp([Item Code],[Text1],0)=0")</pre>


    This example is Control Source property for unbound textbox on form, where Item Description is the field you are looking up from Items table; Item Code is the field in Items that is "case sensitive"; and [Text1] is a textbox on form with the text being compared to Item Code. The [Text1] argument can be replaced with any expression that equates to a text string. The "0" argument for StrComp function tells function to perform a binary (case sensitive) comparison. The return value of 0 ("=0") indicates that the two strings (Item Code and Text1) match.

    HTH

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

    Re: Differentiate between Upper/Lower Case in DLookUp (Access 2000 SR1)

    Using codes that only differ in case, unless this data is entered outside of Access and imported, will probably bite you sooner or later. Except for extraordinary measure like strComp, Access doesn't really distinguish between upper and lower case values. In a combobox, you wouldn't be able to reliably pick one or the other. If you must use both, you would be advised to make the code a display only item and use an underlying numeric value for the real key.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    57
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Differentiate between Upper/Lower Case in DLookUp (Access 2000 SR1)

    Hi Mark,
    Thanks for the help, it worked great.

Posting Permissions

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