Access query where one field is LIKE another

Question

I'm trying to query on on field in one table where it is LIKE a field in another table but am having trouble getting valid results.

I want to find the Pager_ID in tbl_Emergin_Current_Device_Listing_20121126 where it is like the Pager_ID in tbl_AMCOM_PROD.

Some relevant information:

Pager_ID in tbl_Emergin_Current_Device_Listing_20121126 is at most 10 characters and are always numeric characters (example of 10 character Pager_ID: 3145551212).
However, Pager_ID in tbl_AMCOM_PROD can be alpha-numeric ([email protected], which would be the same user.
All data is stored as text.

I want to be able to find "[email protected]" in tbl_Amcom_Prod.Pager_ID when "3145551212" is present in tbl_Emergin_Current_Device_Listing_20121126.Pager_ID. However, with the code below I'm only finding exact matches (EQUAL instead of LIKE).

current code:

SELECT DISTINCT tbl_emergin_current_device_listing_20121126.userrecno,
            tbl_emergin_current_device_listing_20121126.username,
            tbl_emergin_current_device_listing_20121126.department,
            tbl_emergin_current_device_listing_20121126.carriername,
            tbl_emergin_current_device_listing_20121126.protocol,
            tbl_emergin_current_device_listing_20121126.pin,
            tbl_emergin_current_device_listing_20121126.pager_id,
            Iif([tbl_amcom_group_call_leads_and_id].[amcom listing msg id], 
                [tbl_amcom_group_call_leads_and_id].[amcom msg group id], 
                [tbl_amcom_prod].[messaging_id]) 
                AS [Amcom Messaging or Message Group ID]
FROM   ((tbl_emergin_current_device_listing_20121126
     LEFT JOIN tbl_amcom_prod
            ON tbl_emergin_current_device_listing_20121126.pager_id =
               tbl_amcom_prod.pager_id)
    LEFT JOIN tbl_amcom_group_call_leads_and_id
           ON tbl_emergin_current_device_listing_20121126.pager_id =
              tbl_amcom_group_call_leads_and_id.[ams group call lead])
   LEFT JOIN tbl_deactivated_pager_list
          ON tbl_emergin_current_device_listing_20121126.pager_id =
             tbl_deactivated_pager_list.[pager number];

Sample Results:

UserRecNo   UserName    Department  CarrierName Protocol    PIN PAGER_ID    Amcom Messaging or Message Group ID

43  Brown, Lewis    BJH Verizon 0   3145550785  3145550785  3145550785
52  Wyman, Mel  BJH Airtouch (Verizon) (SNPP)   3   3145558597  3145558597  3145558330

I'd also like to see this record but am not with current code:

57  Johnson, Mick   BJH AT&T    3   3145551234  [email protected]  3145559876

What change should I be making?

Thanks in advance!

I could probably do without the extra tables temporarily. They're just used to flag for certain conditions and I can add those back after getting the results pertinent to my post. — mdames

Fionnuala Fionnuala · Accepted Answer · 2013-01-09T14:43:02

Something like:

 SELECT Pager_ID 
 FROM tbl_Amcom_Prod a
 LEFT JOIN  [tbl_Emergin_Current_Device_Listing_20121126] b
 On a.Pager_ID  & "*" Like b.Pager_ID

This will only work in SQL view, not design view.

You could also use a mixture of Instr & Mid.

SELECT IIf(InStr([Pager_ID] & "",".")>0,
           Mid([Pager_ID],1,InStr([Pager_ID],".")-1),[Pager_ID ]) AS PID
FROM [tbl_Amcom_Prod]
WHERE IIf(InStr([Pager_ID] & "",".")>0,
           Mid([Pager_ID],1,InStr([Pager_ID],".")-1),[Pager_ID]) 
           In (SELECT Pager_ID 
               FROM [tbl_Emergin_Current_Device_Listing_20121126])

Access query where one field is LIKE another

1 Answers