MS Access - Join 2 Table Where field1 Like 'field2%'

    I have a small table I am trying to join with a large table, and the smaller table [table1] has has a field [field1] containing doc numbers such as "BPI-1111"; however the larger table [table2] has doc numbers in [field2] that start like [field1] but have training characters that always start with a space "BPI-1111 (787N4-1111)".  I need to join these two tables by these fields.
     I know that there would normally be a % after the text in the second part of the like statement, but I'm not sure how to or if it is even possible to use the % variable with a field.  I know that I'm misusing the % sign below, but I don't know where else to put it.  Can someone help me with the like statement or tell me what I should do differently?  Thanks,

I need this ASAP, so thank you in advance for your help...
SELECT [table2].[field3]
FROM [table2] INNER JOIN [table2] ON [table1].[field1] = [table2].[field2]
WHERE ((([table2].[field2]) Like '[table1].[field1]%' ));

Open in new window

Jon BredensteinerProject ManagerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustav BrockConnect With a Mentor CIOCommented:
You could combine the above suggestions:

  SELECT
    [table2].[field3]
  FROM
    [table2]
  WHERE
    [table2].[field2] Like [table1].[field1] & '*'

/gustav
0
 
Ashish PatelCommented:
Try this.

SELECT [table2].[field3]
FROM [table2] INNER JOIN [table2] ON [table1].[field1] = [table2].[field2]
WHERE [table2].[field2] Like ([table1].[field1] & "%");
0
 
TextReportCommented:
You don't use a join but use the where condition to link the records, also this is Access so the wild character it a * not a %

SELECT [table2].[field3]
FROM [table2]
WHERE [table2].[field2] Like '*[table1].[field1]*'

Thinking about it the join may work

SELECT [table2].[field3]
FROM [table2] INNER JOIN [table2] ON [table2].[field2] Like '*[table1].[field1]*'

But I am not sure

Cheers, Andrew

0
 
Jon BredensteinerProject ManagerAuthor Commented:
asvforce: your code produced: "Syntax error on JOIN operation" and highlighted [table1]

TextReport: your first suggestion returned 0 records
                    your second suggestion produced: "Syntax error on JOIN operation" and highlighted [table2].[field2] Like '*[table1].[field1]*'

cactus_data: your code worked perfectly for me.

Thank you all for your help
0
 
Jon BredensteinerProject ManagerAuthor Commented:
I'll give you guys a little while before I award the points, since asvforce and TextReport were the first to respond to my question, but cactus_data's code actually worked the best.  Thanks again, Jon
0
All Courses

From novice to tech pro — start learning today.