SQL语句中ConcatRelated函数多条件查询及引号使用问题

我使用ConcatRelated函数编写SQL语句，单条件查询可正常执行，但双条件查询无法实现。该双条件写法在Access查询编辑器中可行，但嵌入VBA代码后无法正常运行，相关代码如下：

现有VBA代码 #

ItemsShpdInvSQL = "SELECT InvoiceAllShippedItems.ShipmentID, InvoiceAllShippedItems.ShipType, " _
                & "InvoiceAllShippedItems.BillTo, InvoiceAllShippedItems.CustID AS Consignee, " _
                & "InvoiceAllShippedItems.PayMethod, InvoiceAllShippedItems.PaidInFull, " _
                & "First(InvoiceAllShippedItems.Units) AS Units, " _
                & "'Freight Charges - Ref. WR: ' & (ConcatRelated('[WR]'," _
                & "'[InvoiceAllShippedItems]','[BillTo] =' & [BillTo])) & " _
                & "' - (' & Sum([Chargeable]) & ' ' & [Units] & ' " _
                & ") - Number of Pieces: ' & [NoPieces] AS InvDetails, " _
                & "Sum(InvoiceAllShippedItems.[# of Pieces]) AS NoPieces, " _
                & "Sum(InvoiceAllShippedItems.Chargeable) AS Charge, " _
                & "CustRate([custid],[ShipType]) AS CustomerRate, " _
                & "IIf(([Charge])<6.1,20/[Charge],[CustomerRate]) AS RateMin " _
                & "FROM InvoiceAllShippedItems " _
                & "GROUP BY InvoiceAllShippedItems.ShipmentID, " _
                & "InvoiceAllShippedItems.ShipType, InvoiceAllShippedItems.BillTo, " _
                & "InvoiceAllShippedItems.CustID, InvoiceAllShippedItems.PayMethod, " _
                & "InvoiceAllShippedItems.PaidInFull, InvoiceAllShippedItems.Units, " _
                & "CustRate([custid],[ShipType]) " _
                & "ORDER BY InvoiceAllShippedItems.BillTo;"

查询编辑器中可行的双条件写法 #

concatrelated("[WR]","InvoiceAllShippedItems","[BillTo]&[PayMethod] ='" & [BillTo] & [PayMethod] & "'")

解决方案 #

问题根源在于VBA拼接SQL时的字符串引号处理错误。需要将双条件的逻辑正确转义，确保生成的SQL语句语法合法。修正后的ConcatRelated部分代码如下：

& "'Freight Charges - Ref. WR: ' & (ConcatRelated('[WR]'," _
& "'[InvoiceAllShippedItems]','[BillTo] & [PayMethod] = '''' & [BillTo] & [PayMethod] & ''''') & " _

说明 #

在VBA中拼接包含单引号的SQL字符串时，需要用两个单引号表示一个实际的单引号。上面的写法中，''''会被解析为SQL中的'，确保[BillTo]&[PayMethod]的拼接结果能正确与字段值的拼接字符串进行对比，同时避免SQL语法错误。将这段代码替换原VBA中对应的ConcatRelated部分即可实现双条件查询。

内容的提问来源于stack exchange，提问作者pdesnoye