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
one string should be found in another string. One string is the subset of another screen
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: one string should be found in another string. One string is the subset of another screen
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: one string should be found in another string. One string is the subset of another screen
Hello Hans,
Thanks a lot.
Let me see how it goes.
Regards,
BittenApple
Thanks a lot.
Let me see how it goes.
Regards,
BittenApple
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: one string should be found in another string. One string is the subset of another screen
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
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
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: one string should be found in another string. One string is the subset of another screen
"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".
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".
Best wishes,
Hans
Hans