LIKE query in Access

[MitchJP]

I'm trying to write a query using LIKE. I know how to do this when I'm comparing a field to a string, but how do I do this when comparing a field to another field?

So where we do this:
WHERE table1.field1 LIKE "*joe*"

How do I do this:
WHERE table1.field1 LIKE table2.field2

Any ideas?

Thanks!
MitchJP

 

[bernie10]

So aren't you really just seeing if the fields are equal then?

SELECT Field1, Field2
FROM YourTable
WHERE (((YourTable.Field2)=YourTable.Field1));


If not then how are you deciding when the fields are alike and when they aren't? Do you want when one field starts with the other?

Collen

 

[MitchJP]

No, I'm not seeing if they are equal.

For example, I'm searching for "Australia" within "Once upon a time, I went to Australia".

So normally I'd use LIKE "*Australia*".

But in this case I don't want to compare it to a string like "Australia" but to a whole list of country names.

Does this make sense?

So i one table I have a list of country names, in the other table I have a bunch of news articles. I want to find out where these country names occur in the news articles.

Sample data-
countries:
Australia
USA
France

news articles-
"Australia hits rock bottom"
"France declares war"
"France is great"

I would expect my results to be something like this:

Australia, "Australia hits rock bottom"
France,"France declares war"
France, "France is great"

Does this clarify this??

 

[bernie10]

How about:

SELECT CountriesTable.Field1, ArticlesTable.Field1
FROM CountriesTable, ArticlesTable
WHERE (((ArticlesTable.Field1) Like "*" & CountriesTable.Field1 & "*"));

Hope this helps,
Collen

 

[MitchJP]

That didn't work. I tried something similar and had no luck.

 

[fstrahberger]

Hi,

try this one:

SELECT *
FROM CountriesTable, ArticlesTable
WHERE (((ArticlesTable.Field1) Like '*' & [CountriesTable.Field1] & '*'));

 

[PHV]

Perhaps this ?
SELECT C.Field1, A.Field1
FROM CountriesTable AS C, ArticlesTable AS A
WHERE A.Field1 Like "*" & Trim(C.Field1) & "*";

Are CountriesTable and ArticlesTable both local tables or linked ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MitchJP]

they are local tables.

 

[MitchJP]

Hey fstrahberger, that worked (or at least I'm getting results now). Thanks! I'll validate it and let you know..

Thanks again,
MitchJP