Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: Case Sensitive serch using the IN Keyword

    Hi,

    I'm new to access (and haven't used SQL in a few years). I have a large dataset that I'm dealing with and I'm trying to to narrow down the results and I want to convert the following query into a case sensitive one:

    SELECT *
    FROM TABLE
    WHERE code IN ('AAA','BBB','CCC','DDD');

    (and I don't want aaa, AaA, etc returned in the results)
    From searching it seems like if I had only one thing to search for I'd use

    SELECT *
    FROM TABLE
    WHERE StrComp(code,'AAA',0)=0;

    Without excessively using OR's (there are a lot more codes than just the 4 I listed above), is there a way to use StrComp with IN. Or a more efficient way to do a case sensitive search?
    Last edited by namit101; 04-15-11 at 08:48.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I can see one solution but the performance would be terrible:
    Code:
    Public Function CodeString(ByVal SomeString As String) As String
    
        Do Until Len(SomeString) = 0
            CodeString = CodeString & Asc(SomeString)
            SomeString = Mid(SomeString, 2)
        Loop
        
    End Function
    Then:
    Code:
    SELECT Table.*
    FROM Table
    WHERE CodeString(Code) IN ( 
    CodeString("AAA"), 
    CodeString("BBB"), 
    CodeString("CCC"), 
    CodeString("DDD") 
    );
    Have a nice day!

Tags for this Thread

Posting Permissions

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