one string should be found in another string. One string is the subset of another screen

[BittenApple]

Hello team,

I have a string in one field in a table, in the second table, I have a field that is string, and it is code definition but it has code as well.

I want to find if the first field is a substring of the second field, to grab the code for that string from second table:
First table second table
definition definition code
cervical cervical cancer screening 123

If we can find cervical in cervical cancer screening, then grab code 123.

How can we do that?

Regards,
Bittenapple

[HansV]

Create a query based on the first table.
Add the Definition field (and other fields if you need them) to the query grid.
Add a calculated column:

Code: DLookup("Code", "NameOfSecondTable", "Definition LIKE '*" & [Definition] & "*'")

where NameOfSecondTable is (you guessed it) the name of the second table.

[BittenApple]

Hello Hans,
Thanks a lot.
Let me see how it goes.
Regards,
BittenApple

[BittenApple]

Hello Hans,
I couldn't understand this part:
"Definition LIKE '*" & [Definition] & "*'")
Is this a concatenate?

If one string in the first table is found in another string in the second table, then return the code that belongs to that string. (This is the logic)
If breast from first table is found in breast cancer screening in second table, then there is a field in second table, named code, then bring that code.
Respectfully,
BittenApple

[HansV]

"Definition LIKE '*" & [Definition] & "*'" is the criteria argument of DLookup. It tells Access what to look for.
Let's say that a record in the first table has heart in the Definition field. The expression "Definition LIKE '*" & [Definition] & "*'" evaluates to

"Definition Like '*heart*'"

* stands for any characters (including none), so *heart* means any string containing "heart", such as "heart disease", "weak heart" and "severe heart condition".