Comparing multiple fields within the same row in a single table

Posted on 2012-08-15
Last Modified: 2012-08-15
Column A                                 Column B
Lorem Ipsum Tango                Tango

I'm trying to write a query  that will search all rows in Column A and see if they contain the contents of Column B.

This one returned NO rows:
FROM [Table1]
WHERE ((([Table1].[ColumnA]) Like "*[Table1].[ColumnB]*"));

This one returns all rows with C, O, L, U, M...., or B in them:
FROM [Table1]
WHERE ((([Table1].[ColumnA]) Like "*[ColumnB]*"));

How to I search for Column B within Column A without it matching exactly?
i.e. *[ColumnB]*

Thank you!
Question by:dgran
    LVL 29

    Accepted Solution

    SELECT DISTINCTROW [Table1].[ColumnA]
    FROM [Table1]
    WHERE [Table1].[ColumnA] Like "*" & [Table1].[ColumnB] & "*"

    Author Closing Comment

    I can't believe I didn't think of that!  I've use that before many times.  Mostly for filling a field with the values from multiple fields.
    Thank you!
    LVL 29

    Expert Comment

    Every now and then we all just get a brain fart.  You probably would have figured this out eventually.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now