Using dimension as field name

[Dachande]

Hello,

I have a loop code that steps through and creates a string from the counter, for example it goes P01, P02, P03 etc until it gets to 12. This is fine. But I want to use this as criteria in an update query (this works as well) and also the field name, as I have 12 fields in a table which are named the same. Please could someone tell where I am going wrong in the code. The problem lies at the "SET [product_group_year1].'" & per & "' = " as I have used the actual field name and it works.

cn.Execute "UPDATE Product_Group_Year1 INNER JOIN Product_Sub_Temp ON " & _
"[Product_Group_Year1].[ProductGroup] = [Product_Sub_Temp].[Product Group] SET [Product_Group_Year1].'" & per & "' = [Product_Sub_Temp].[niv] " & _
"WHERE [Product_Sub_Temp].[Period]= '" & per & "' AND [Product_Sub_Temp].[Year]= '" & YearFrom & "';"

Thanks

Mark

 

[Travis]

When dynamically using field names use [ ].

Change this [product_group_year1].'" & per & "' = "
To this [product_group_year1].[" & per & "] = "