CODEWITHSUNDEEP
sqlms-accesssubstringms-access-2016
user3436361
user3436361

Reputation: 1

Find all substring from another column - ms access

tblCode is a dummy table from which I want to pull records. Row-wise each code values (example 32) should be checked in subcode and should return all records if found in any part of string.

+----+------+----------+
| ID | code |  subCode |
+----+------+----------+
| 1  |      | 234322   |
+----+------+----------+
| 5  | 32   | 8999999  |
+----+------+----------+
| 2  |      | 32       |
+----+------+----------+
| 7  |      | 45       |
+----+------+----------+
| 6  |      | 56565465 |
+----+------+----------+
| 3  | 45   | 6767676  |
+----+------+----------+
| 4  |      | 7894522  |
+----+------+----------+
| 8  |      | 83       |
+----+------+----------+

For instance, when I run following query, I get 2 records

`SELECT * from tblCode where subCode like '*45*'`

enter image description here

The actual table is huge with several code & subCode. I have tried following query but returns incorrect results

`SELECT * from tblCode where subCode like '*'& code &'*'`

Upvotes: 0

Views: 180

Answers (1)

Gustav
Gustav

Reputation: 56016

A Cartesian (multiplying) query will do:

SELECT 
    tblCode.ID, 
    tblCode_1.code, 
    tblCode.subCode
FROM 
    tblCode, 
    tblCode AS tblCode_1
WHERE 
    tblCode_1.code Is Not Null AND 
    tblCode.subCode Like "*" & [tblCode_1]![code] & "*";

Output:

enter image description here

Upvotes: 1

Related Questions