wildcard like, not returning desired output

[Lifeseeker]

Hi,

I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?

I'm basically limiting my records to those found in tbl_site based on a wildcard "like".

Any help is much appreciated.

Thanks

 

[vbaInet]

Can you please post the SQL of the query here.

 

[Lifeseeker]

Can you please post the SQL of the query here.

SELECT tbl_records.ID, tbl_records.name
FROM tbl_records, tbl_site
WHERE (((tbl_records.name) Like "*" & [tbl_site].[site_name] & "*"));

 

[plog]

Actually you are not, you effectively have an even more stringent set of criteria in that query. You are using an INNER JOIN between your two tables. An INNER JOIN is essentially criteria--it will only show records where the values in both sides of the join are equal.

Because of that join your query is set to show only fields where tbl_records.name equals tbl_site.site_name. The criteria itself is useless because it is less restrictive than the INNER JOIN.

 

[plog]

The SQL you posted isn't the same as the one in your database. The database is using an INNER JOIN, the code you just posted does not have that.

The SQL you posted returns different results than the one in the database you posted.

 

[vbaInet]

I understand what you're trying to do. Your SQL should be this:

SELECT tbl_records.ID, tbl_records.name
FROM tbl_records INNER JOIN tbl_site ON tbl_site.site_name LIKE "*" & tbl_records.name & "*";

 

[Lifeseeker]

Ok, I'm attaching a new database to minimize confusion.

This is the code for test1 query.

SELECT tbl_records.ID, tbl_records.name
FROM tbl_records INNER JOIN tbl_site ON tbl_records.name = tbl_site.site_name
WHERE (((tbl_records.name) Like "*" & [tbl_site].[site_name] & "*"));

I can see that there is an inner join used.

I try to get rid of the tbl_site in the query design window and still use the & &, it asks me for the source still. So I decided to just leave the tbl_site in there but not connect the two tables. Please see test2, and its code is this:

SELECT tbl_records.ID, tbl_records.name
FROM tbl_records, tbl_site
WHERE (((tbl_records.name) Like "*" & [tbl_site].[site_name] & "*"));

But what if there are repeats in the tbl_site in the name, would this be a cross-join???

Thanks

 

[Lifeseeker]

I understand what you're trying to do. Your SQL should be this:

SELECT tbl_records.ID, tbl_records.name
FROM tbl_records INNER JOIN tbl_site ON tbl_site.site_name LIKE "*" & tbl_records.name & "*";

"aaaaa" is still not returned. Shouldn't it return it in the output?

 

[plog]

Did you try the SQL you posted in your 2nd post? That should do it.

Additionally vba's code would work if you swapped the fields in the JOIN.

SELECT tbl_records.ID, tbl_records.name
FROM tbl_records INNER JOIN tbl_site ON tbl_records.name  LIKE "*" & tbl_site.site_name & "*";

 

[vbaInet]

It's a matter of switching the fields around in the SQL statement.

 

[vbaInet]

Did you try the SQL you posted in your 2nd post? That should do it.

It will do it but it's just the wrong join.

 

[Lifeseeker]

It's a matter of switching the fields around in the SQL statement.

ahh cool! Got it, thank you!

 

[vbaInet]

No problemo!