MS Access - SQL Concatenate works - VBA does not

Question

I created a query to concatenate two fields in a table into a third field in the same table.

• Table: [mif_999_sf_item_creation_subcategory_groups]
• Field1: [Product_Group_Code__c]
• Field2: [Subcategory_Group_Code__c]
• Field1&2: [CCat_PrdGrpCd_n_SubCatCd]

The query worked, here is the SQL code:

UPDATE mif_999_sf_item_creation_subcategory_groups SET mif_999_sf_item_creation_subcategory_groups.CCat_PrdGrpCd_n_SubCatCd = [Product_Group_Code__c] & "_" & [Subcategory_Group_Code__c];

I used AllenBrowne's code to convert the SQL code to VBA.

But when I pasted the VBA code into a VBA module and ran it and nothing happened. I did not receive any error messages but neither were the two columns concatenated.

Sub Test()

strSql = "UPDATE mif_999_sf_item_creation_subcategory_groups SET mif_999_sf_item_creation_subcategory_groups.CCat_PrdGrpCd_n_SubCatCd = [Product_Group_Code__c] & ""_"" & [Subcategory_Group_Code__c];"

End Sub

I tried looking for other examples on the web and on this site but nothing worked for me.

Any ideas as to what I'm doing wrong?

Answer 1

You are not getting any errors because it does not exist. It is working fine what you told to do. I guess you want to run query assigned in the variable strSql ?

In that case, you need to do it like below :

Sub Test()

    strSql = "UPDATE mif_999_sf_item_creation_subcategory_groups SET mif_999_sf_item_creation_subcategory_groups.CCat_PrdGrpCd_n_SubCatCd = [Product_Group_Code__c] & ""_"" & [Subcategory_Group_Code__c];"

    DoCmd.RunSQL strSql

End Sub