MS Access: Find a substring from a table inside a string in another table

|Dear all,

I am working with MS Access.

I have two tables:

TABLE2 - with the field texts(char)

and

Table1 - spanish (text 30 chars)

I want find on table2.texts each register placed on Table1.spanish

Examples (that not works):

rs.open "select * from table1, table2 where table1.spanish like '%" & table2.texts & "%' ", con

rs.open "SELECT table1.*, table2.texts FROM table1 INNER JOIN table2   ON table2.texts LIKE ('%" &  table1.spanish & "%') ", con

Example: table2.texts: "Y eso ha sido exactamente lo sucedido en los últimos 10 años con un sector bancario que en 2007 sobrevaloraba tanto su solidez
 "
table1.spanish: "sector bancario"

But these codes not works.

Could someone please place a code that works?

Thanks in advance
BOB KUSPEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
How these tables are related ? Id column or some other column ?

Go to SQL and execute below and see if you are getting the results. sometext - pass some value present in the DB.

select * from table1 Where table1.spanish like '%sometext%'
0
BOB KUSPEAuthor Commented:
Are not related. The datasources of table1 is distinct from source of table2.

I am working with MSAccess database.

Sorry, but this answers does not solve the problem, because I am working with 1 column from table 1 and one column of table2, not Request.QueryString
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if you're using MS Access, I believe you need to use * instead of using %

If that doesn't resolve the issue, it would help to know what you mean by "not working"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BOB KUSPEAuthor Commented:
Thank you Big Monty

It works better with * instead %. Now, the error message is: Sorry, no matching record was found.

My select:

rs.open "select * from table2 , table1 where texts like ('*spanish*')  ", con,

table2.jpgtable1.jpg
0
Pawan KumarDatabase ExpertCommented:
First try this and see if you are getting records.

select * from table1 where texts like ('*spanish*')
0
BOB KUSPEAuthor Commented:
Unfortunately not. "No value given for one or more required parameters."
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
after reading and understanding this a bit more, you don't want to use LIKE in this case, as you're not trying to find a string literal, but rather tags from another table. I have a meeting to get to, but i think this is going to be a bit more complex, as you're trying to match up and search on strings from another table. Hopefully another ACCESS expert can offer some insight.
1
Pawan KumarDatabase ExpertCommented:
Please try like this -

SELECT a.*, b.texts FROM table1 as a INNER JOIN table2  as b ON a.spanish LIKE "*" &  b.texts & "*"
0
BOB KUSPEAuthor Commented:
Type mismatch: '[string: "SELECT a.*, b.texts "]'
0
Pawan KumarDatabase ExpertCommented:
Updated.

SELECT  * FROM table1 as a INNER JOIN table2  as b ON a.spanish LIKE "*" &  b.texts & "*"
0
BOB KUSPEAuthor Commented:
Well,

It works partially if written:

SELECT  * FROM table1 as a INNER JOIN table2  as b ON a.spanish LIKE '*' &  b.texts & '*'

with the message: Sorry, no matching record was found, but there are records that matching
0
Pawan KumarDatabase ExpertCommented:
Please try the other way round-

SELECT  * FROM table1 as a INNER JOIN table2  as b ON b.texts LIKE '*' &  a.spanish & '*'
1
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
SELECT texts
FROM table2
WHERE EXISTS (SELECT * FROM table1 WHERE table2.texts LIKE '*'&table1.spanish&'*');

Open in new window


I found this to work with my test db
29069415.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fabrice LambertFabrice LambertCommented:
The type mismatch is because of the double quotes.
Also, I think that Pawan's joint statement is on the wrong order.
This worked for me:
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = vbNullString
strSQL = strSQL & "SELECT table1.*, Table2.texts" & vbCrLf
strSQL = strSQL & "FROM Table1 INNER JOIN Table2" & vbCrLf
strSQL = strSQL & "         ON Table2.texts Like ""*"" & [Table1].[spanish] & ""*"";"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

rs.Close
Set rs = Nothing
Set db = Nothing

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
What I had to do to make this work is copy the phrase, "sector bancario" directly from table two and paste to table 1
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I added some more records and still works
29069415.accdb
0
Pawan KumarDatabase ExpertCommented:
Have u tried like -

SELECT  * FROM table1 as a INNER JOIN table2  as b ON b.texts LIKE '*' &  a.spanish & '*'
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Pawan, are you testing this in Access?
0
BOB KUSPEAuthor Commented:
Hi Scott Fell, EE MVE

I am working with MSAccess 2003, and asp classic, Unfortunately I cannot open your database because I am working with an old version of MSAccess

rs.open "SELECT texts FROM table2 WHERE EXISTS (SELECT * FROM table1 WHERE table2.texts LIKE '*'&table1.spanish&'*') ",con

Gives a message: no matching record was found.
0
BOB KUSPEAuthor Commented:
Hi Fabrice

If I am not wrong it is a client solution? Unfortunately I am working with a server side and I don´t know much more about VB only VB script =asp classic using a connexion OLEDB 4.0
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
2003 version
29069415.mdb
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
> Gives a message: no matching record was found.

Make double sure there is a matching record. Like I said, to make it work, I had to copy the text from your long sentence.  Using your supplied data did not work either.
0
BOB KUSPEAuthor Commented:
Great. Are working fine to you?

I did rs.open "SELECT texts FROM table2 WHERE EXISTS (SELECT * FROM table1 WHERE table2.texts LIKE '*'&table1.spanish&'*') ",con

But it gives the message: Sorry, no matching record was found.

I don´t understand what happens
0
BOB KUSPEAuthor Commented:
Now I am using your database but that gives the message "Sorry, no matching record was found."
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Go to table 2 in Access and select some sample text. Then paste that to table1.  Then run the query.

Next, create a new sentence and add one word.

When you runt the query in the db, you can see it works.  If not, please upload your database.
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
> I did rs.open "SELECT texts FROM table2 WHERE EXISTS (SELECT * FROM table1 WHERE table2.texts LIKE '*'&table1.spanish&'*') ",con


Please try running the query in the database first.  Also, in access you can treat the query as a table.   Try targeting query1 after uploading my database or creating a query on your own.

 rs.open "SELECT texts FROM Query1 ",con
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
My table has

Table1.spanish
sector bancario
sector
stuff
sentence

Table2.texts
Y eso ha sido exactamente lo sucedido en los últimos 10 años con un sector bancario que en 2007 sobrevaloraba tanto su solidez
more things
this should not work
stuff should work
another sentence and this will work too

Query1.texts
Y eso ha sido exactamente lo sucedido en los últimos 10 años con un sector bancario que en 2007 sobrevaloraba tanto su solidez
stuff should work
another sentence and this will work too
1
BOB KUSPEAuthor Commented:
You are right. There is a problem in the database. When I run the query on the db. It gives "no matching record was found.", but the records exists"

I will reinstall the database

Thank you
0
BOB KUSPEAuthor Commented:
Thank you
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I don't know where  you are in the project, but if you are just starting out, I do suggest to use sql server express https://www.microsoft.com/web/platform/database.aspx.  It is easier to work with for a website. There is a learning curve but it goes quick. Just a few gotchas such as wildcards as noted here and different field types.  It may set you back a day or two but in the end you will be happier.  I have not used Access for a web app in probably the last 14 years or so.

Best of luck on your project!
1
BOB KUSPEAuthor Commented:
I agree with you. The MSAccess is not the best database. But I had a problem with my main computer (with Windows 7) after a bad installation of Linux and while I do not buy an external hd to save my data I am using another desk computer with Windows XP. Also outdated!

Thank you
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I don't use an external drive to save my development area either. Instead,i do all my work on my dropbox folder and whenever I am connected to the internet (99% of the time), there is an automatic back up to the cloud. If I mess up locally I can restore from the cloud by any saved version.  On my live server, I have a continuous back up for my database server.
1
BOB KUSPEAuthor Commented:
EUREKA!!! I got!


rs.open "SELECT table2.texts, table1.spanish FROM table2 inner join table1 on table2.texts like '%'&table1.spanish&'%'  ",con
0
BOB KUSPEAuthor Commented:
but only on MSACCESS 2000
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.