MS Access Query Dlookup using Like?

Question

I have two Tables. "RPT_CHG" and "OPTIONS". In RPT_CHG, If the field [USER2] can be found in the Table OPTIONS Field [ApplyToProductCodes] Then I want it to return to me a list of ID's that contain [USER2] in [ApplyToPRoductCodes]

(I.E.
RPT_CHG Field [USER2] = ICE-GEM0450A

OPTIONS Field [ApplyToProductCodes] has Two Records with the ICE-GEM0450A in it.

ID# 36756 contains
ICE-GEM0450A,ICE-GEM0450W,ICE-GEM0650A,ICE-GEM0650W,ICE-GEM0956A,ICE-GEM0956W,ICE-ICE0250FA,ICE-ICE0250FT,ICE-ICE0250FW,ICE-ICE0250HA,ICE-ICE0250HT,ICE-ICE0250HW,ICE-ICE0320FA,ICE-ICE0320FW,ICE-ICE0320HA,ICE-ICE0320HW,ICE-ICE0400FA,ICE-ICE0400FT,ICE-ICE0400FW,ICE-ICE0400HA,ICE-ICE0400HT,ICE-ICE0400HW,ICE-ICE0406FA,ICE-ICE0406FW,ICE-ICE0406HA,ICE-ICE0406HW,ICE-ICE0500FA,ICE-ICE0500FT,ICE-ICE0500FW,ICE-ICE0500HA,ICE-ICE0500HT,ICE-ICE0500HW,ICE-ICE0520FA,ICE-ICE0520FW,ICE-ICE0520HA,ICE-ICE0520HW,ICE-ICE0606FA,ICE-ICE0606FT,ICE-ICE0606FW,ICE-ICE0606HA,ICE-ICE0606HT,ICE-ICE0606HW,ICE-ICE0806FA,ICE-ICE0806FW,ICE-ICE0806HA,ICE-ICE0806HW,ICEO-GEM0450A,ICEO-GEM0650A,ICEO-GEM0956A,ICEO-ICE0250FA,ICEO-ICE0250HA,ICEO-ICE0320FA,ICEO-ICE0320HA,ICEO-ICE0400FA,ICEO-ICE0400HA,ICEO-ICE0500FA,ICEO-ICE0500HA,ICEO-ICE0520FA,ICEO-ICE0520HA,ICEO-ICE0606FA,ICEO-ICE0606HA,ICEO-ICE0806FA,ICEO-ICE0806HA

And ID# 36757 contains
ICE-GEM0450W,ICE-GEM0450A,ICE-GEM0650W,ICE-GEM0956W,ICE-ICE0250FT,ICE-ICE0250FW,ICE-ICE0250HT,ICE-ICE0250HW,ICE-ICE0320FA,ICE-ICE0320FW,ICE-ICE0320HA,ICE-ICE0320HW,ICE-ICE0400FT,ICE-ICE0400FW,ICE-ICE0400HT,ICE-ICE0400HW,ICE-ICE0406FA,ICE-ICE0406FW,ICE-ICE0406HA,ICE-ICE0406HW,ICE-ICE0500FT,ICE-ICE0500FW,ICE-ICE0500HT,ICE-ICE0500HW,ICE-ICE0520FA,ICE-ICE0520FW,ICE-ICE0520HA,ICE-ICE0520HW,ICE-ICE0606FT,ICE-ICE0606FW,ICE-ICE0606HT,ICE-ICE0606HW,ICE-ICE0806FW,ICE-ICE0806HW,ICE-ICE1006FW,ICE-ICE1007FW,ICE-ICE1007HA,ICE-ICE1007HW,ICE-ICE1406FA,ICE-ICE1406FW,ICE-ICE1406HA,ICE-ICE1406HW,ICE-ICE1407FA,ICE-ICE1407FW,ICE-ICE1407HA,ICE-ICE1407HW,ICE-ICE1806FW,ICE-ICE1806HW,ICE-ICE1807FW,ICE-ICE1807HW,ICE-ICE2106FW,ICE-ICE2106HW,ICE-ICE2107FW,ICE-ICE2107HW,ICE-MFI0500W,ICE-MFI0800W,ICE-MFI1256W,ICEO-GEM0450A,ICEO-GEM0650A,ICEO-GEM0956A,ICEO-ICE0250FA,ICEO-ICE0250HA,ICEO-ICE0320FA,ICEO-ICE0320HA,ICEO-ICE0400FA,ICEO-ICE0400HA,ICEO-ICE0500FA,ICEO-ICE0500HA,ICEO-ICE0520FA,ICEO-ICE0520HA,ICEO-ICE0606FA,ICEO-ICE0606HA,ICEO-ICE0806FA,ICEO-ICE0806HA,ICEO-ICE1006FA,ICEO-ICE1006HA,ICEO-ICE1406FA,ICEO-ICE1406HA,ICEO-ICE1806FW,ICEO-ICE1806HW,ICEO-ICE2106FW,ICEO-ICE2106HW,ICEO-MFI0500A,ICEO-MFI0800A,ICEO-MFI1256A

So my Query would spit out

ID
36756
36757

I have tried several possible methods and none of them seem to do what I want.

I attempted:

ID: DLookUp("[ID]","OPTIONS","[USER2]" Like [ApplyToProductCodes])

This did not give the desired result of some 1300 records to the First Available record. What am I missing? Should I be using a different method? Please advise. Thank you!

Answer 1

I think this is the query you seek:

SELECT RPT_CHG.USER2, OPTIONS.ID
FROM RPT_CHG, OPTIONS
WHERE (((OPTIONS.ApplyToProductCodes) Like "*" & [RPT_CHG].[USER2] & "*"));

Based on my (slightly modified) test data it returns

USER2            ID
---------------  -----
ICE-GEM0450A     36756
ICE-GEM0450A     36757
IAMSOSMART-SMRT  36757