Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2010
    Location
    Chicago, Illinois, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This should be easy for the Woody's crowd. I need to write a query where the result set only includes rows where Column B is not a substring of Column A (and vice versa). Having fits with NOT LIKE*[A]*. Can someone point me in the right direction?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is this in Access, SQL Server, or some other database engine? In general you need an OR condition in the SQL string something along the lines of
    WHERE [ColumnB] NOT LIKE '*[ColumnA]*' OR [ColumnA] NOT LIKE '*[ColumnB]*'
    but the syntax varies depending on what database engine you are using. The sample above is formatted more or less for Access since your title suggests that is what you are using, but ADPs in Access use a different syntax. This is a little easier to visualize if you use the QBE grid.
    Wendell

  3. #3
    New Lounger
    Join Date
    Oct 2010
    Location
    Chicago, Illinois, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    MS Access 2007 is the application I am using, and I am using the QBE grid.

    I have a situation where Column B is generally a subset of Column A, but not always. I want to find the situation where it is not.
    Using the QBE, I have the following criteria on Column A-

    Not Like '*[Column B]*'.


    The rare situations where Column A is a subset of B are irrelevant here- if I can get the above to work, then I can extend it. No problems.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What about this.

    Not like "*" & [Column B] & "*"
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I should know better than to do air code! Your syntax should work, John.
    Wendell

Posting Permissions

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