Unrelated table as criteria

[susanmgarrett]

I have a main table that contains a text string in a field called Category.

Normally, I'd just put a LIKE "*looking for*" string in the query criteria field.

But I have over 2,000 text strings that I need to use as criteria (a one-time only proposition, thank heavens). I'm looking for any form of the criteria text string (it's definitely a LIKE situation). I've put those text strings into a field called akKwd in a table called Criteria.

SELECT [Maintable].[Category]
FROM [Maintable]
WHERE ((([Maintable].[Category]) Like "*[Criteria].[akKwd]*"));

I'm getting 7 matches, where it should be much closer to several hundred.

Can anyone give me a clue? This is the first time I've ever tried to use a table outside of a relationship in a query.

Thanks!

 

[georgedwilkinson]

What are the 7 matches?

I imagine what you want is:

SELECT [Maintable].[Category]
FROM [Maintable], Criteria
WHERE ((([Maintable].[Category]) Like '*' & [Criteria].[akKwd] & '*'));

though I have no clue since I don't have all the information (like what is an akKwd?).

 

[susanmgarrett]

Absolutely perfect!

Thank you very much - that was precisely what I was missing. I didn't know to add the second table in the FROM section or how to handle the asterisks properly.

That works properly now. Thanks for the lesson!

 

[georgedwilkinson]

Cool! Always glad to lend a hand.