how to find certain words based on table without overwriting

[vinwin06]

Hello,

I have a list of words available in one particular table, based on those records i need to match it up with one specific column on another table. Based on that match i need to update the same. below is answer for this question;

Design a new query. Select your data table and your words table. No join line between them.

Drag datatable.* to the design grid, and the field to check. In the criteria for that field write:

Like '*' & [wordstable]![wordsfield] & "*"

(of course you change this to your object names)

But now my another question is that how i can do update without replacing the existing one. Like for example " Peter is engineering degree".

In this example if i have words like ring and degree on my reference table, then as per the solution it will update the degree first then later if its find ring on the text then it will overwrite the degree on the particular column. How i can prevent this?

[HansV]

Sorry, I don't understand what you want. Can you give some examples of what you want to replace, and how?

[vinwin06]

In my example two words are there in same sentence so based on reference table words I have I have to search it in the sentence if it's matching then one word get updated but what's happening is.. If second word also there then it replace the first one. But I don't want to that. Please see my sample database.

[HansV]

So what do you want the result to be? Do you want to return only the first word found, or all words, or...?

[vinwin06]

If it's possible first word, second word. Then in that case it will difficult know like we will have more words on same bucket.

So just keep it with first and should not replaced or overwrite by new one.

I have another request but this is not part of this one but it's related only next request is about without giving any word reference can access able to find out the names from text itself? like common objects name. gasket, pipe, wrench etc. i know its difficult but is it possible todo ?

[HansV]

I have attached a version that returns multiple words, and that avoids returning parts of words. For example, if "ring" is in your list of words, the query won't return "engineering". The query uses a custom VBA function DConcat.

It would be possible to create a list of ALL words occurring in the text field, but then you'd get words such as "I", "a", "an", "in", "on", "and", "the" etc. too. To exclude those would be complicated.

Certain word search.zip

[vinwin06]

Then for the another request we can create another query to remove those words like I, an, on etc know?
So u mean to say we don't need to give list of words it Can able find by the query itself based on the text?

Thanks for ur help.. Really awesome...

[HansV]

So you want the Match field to list ALL words from the Text field except for small words such as "a", "an" etc.?

[vinwin06]

Yes ur right. Is there a way to identify objects names only?

[HansV]

It would be possible to list ALL words except short ones, but to identify object names only would require either a list of words, as you are using now, or some kind of artificial intelligence; the latter is far beyond the scope of this forum.

[vinwin06]

Then can you suggest me that how do find out except short ones, then based on some manual work will remove the unwanted ones.

[HansV]

The attached version contains a VBA module with a procedure CreateWordList. When you run it, the Match field in Table1 will be filled with all words from the Text field of length 4 and more.

Certain word search2.zip

[vinwin06]

Thanks Hans for the file. Is it possible to store it as a separate table instead of storing the same table. Also plz store it as each word as one record.

[HansV]

In the attached version, I have added a procedure CreateWordList2. It will fill Table2 with a separate record for each word of length 4 or more it encounters.

Certain word search2.zip