Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

New to Sql/VBA - How to concatenate two fields

Copper Contributor

Hi, I've been using access for a long time, but have always used Macro builder etc..very little experience with SQL. 

 

I had someone write a query for me that updates data in a table - (qry_CreatePriceAddTemplate) is actually a table..I plan on changing that name.

 

I am trying to add a new calculated field: XREFID: concatenate [Indirect Contract]&[NDC Full]

so that it will update/populate the existing field in the table (qry_CreatePriceAddTemplate).

 

Any help would be much appreciated!

 

 

 

 

 

strSQL = "DELETE * FROM qry_CreatePriceAddTemplate"
    DoCmd.RunSQL strSQL
    
    strSQL = "INSERT INTO qry_CreatePriceAddTemplate SELECT [tbl_Contract ID].[Indirect Contract], [tbl_Contract ID].[Alt Contract], "
    strSQL = strSQL & "[tbl_Contract ID].[Contract Description], tbl_NDCXREF.[NDC Full], "
    strSQL = strSQL & "tbl_NDCXREF.[MATERIAL DESCRIPTION], '' AS Price, '' AS Effective, "
    strSQL = strSQL & Chr(34) & "12/31/9999" & Chr(34) & " AS [End Date]"
    strSQL = strSQL & " FROM tbl_NDCXREF, [tbl_Contract ID]"
    strSQL = strSQL & " WHERE tbl_NDCXREF.[MATERIAL DESCRIPTION] IN (" & strSelectedProduct & ")"
    strSQL = strSQL & " And [tbl_Contract ID].[Contract Description] IN (" & strSelectedContractDesc & ")"
    strSQL = strSQL & " GROUP BY [tbl_Contract ID].[Indirect Contract], [tbl_Contract ID].[Alt Contract], "
    strSQL = strSQL & "[tbl_Contract ID].[Contract Description], tbl_NDCXREF.[NDC Full], "
    strSQL = strSQL & "tbl_NDCXREF.[MATERIAL DESCRIPTION], ''," & Chr(34) & "12/31/9999" & Chr(34) & ", ''"
    
    DoCmd.RunSQL strSQL

 

 

 

1 Reply
strSQL = "INSERT INTO qry_CreatePriceAddTemplate SELECT [tbl_Contract ID].[Indirect Contract], [tbl_Contract ID].[Alt Contract], "
strSQL = strSQL & "[tbl_Contract ID].[Contract Description], tbl_NDCXREF.[NDC Full], "
strSQL = strSQL & "tbl_NDCXREF.[MATERIAL DESCRIPTION], '' AS Price, '' AS Effective, "
strSQL = strSQL & Chr(34) & "12/31/9999" & Chr(34) & " AS [End Date]", [Indirect Contract]&[NDC Full] AS XREFID
strSQL = strSQL & " FROM tbl_NDCXREF, [tbl_Contract ID]"
strSQL = strSQL & " WHERE tbl_NDCXREF.[MATERIAL DESCRIPTION] IN (" & strSelectedProduct & ")"
strSQL = strSQL & " And [tbl_Contract ID].[Contract Description] IN (" & strSelectedContractDesc & ")"
strSQL = strSQL & " GROUP BY [tbl_Contract ID].[Indirect Contract], [tbl_Contract ID].[Alt Contract], "
strSQL = strSQL & "[tbl_Contract ID].[Contract Description], tbl_NDCXREF.[NDC Full], "
strSQL = strSQL & "tbl_NDCXREF.[MATERIAL DESCRIPTION], ''," & Chr(34) & "12/31/9999" & Chr(34) & ", '', [Indirect Contract]&[NDC Full]"