Update Inner Join SQL string help

[keith_shoaf]

Hello everyone! I am trying to figure out how to get an SQL string correct and need some assistance.
I have a table of data that is comprised of approximately 30 excel files. Field1 and Field2 are indicator codes that when combined (Field1.Field2) indicate a department. The department for each set of combined indicators is stored on a reference table (examples of both tables below). I need an Update Inner Join SQL statement that will do a join ON Field1 & “.” & Field2 = Ind_Cd

table1:

Field1	Field2	Field3	Field4
1212	9000	BA
0100	56SA	ZZ
0300	6010	04

<tbody>
</tbody>

table2:

Ind_Cd	Nm
1212.9000	DepartmentA
0100.56SA	DepartmentB
0300.6010	DepartmentC
0500.1156	DepartmentD

<tbody>
</tbody>

Here is what I have now in my VBA procedure:

DoCmd.RunSQL “UPDATE table1 INNER JOIN table2 ON table1.Field1” & “.” & “table1.Field2 = table2.Ind_Cd SET table1.Field4 = table2.Nm”

But this doesn’t recognize the ON table1.Field1” & “.” & “table1.Field2. When I try to run this, it gives an input box prompt for table1.Field1.table1Field2
Is there a way to do this? Thanks!

 

[Fazza]

untested. try

“UPDATE table1 INNER JOIN table2 ON table1.Field1 & '.' & table1.Field2 = table2.Ind_Cd SET table1.Field4 = table2.Nm”

or maybe
“UPDATE table1 INNER JOIN table2 ON (table1.Field1 & '.' & table1.Field2) = table2.Ind_Cd SET table1.Field4 = table2.Nm”

 

[james_lankford]

another guess

UPDATE 
  table1 
INNER JOIN 
  table2 
    ON 
    table1.Field1 = left(table2.Ind_Cd, 4) 
    and 
    table1.Field2 = right(table2.Ind_Cd, 4)
SET 
  table1.Field4 = table2.Nm

but I doubt this will work

 

[Fazza]

can't see why it wouldn't work, James. of course it assumes the field length and we don't know if that is a correct assumption

the issue was really not the SQL , rather the syntax of the string created in VBA