我收到此錯誤是因為嘗試將 Itemnr2 變數作為單元格值。如果我在 ItemNr1 中使用普通值，則一切正常。但是我需要使用單元格中的值，因為它會不時更改，所以不想每次都更改代碼。如何將值從單元格放入 SQL 代碼以使其正確？
當我在所有值都是數字的其他列中使用它時，它正在作業。在我需要的這一列中，有像 b2b_pkl 這樣的值，因此在將 nvarchar 值轉換為資料型別 int 錯誤時，這些轉換失敗。
PA = get_market_setting(market, "PA")
POS = get_market_setting(market, "POS")
NetAmount = get_market_setting(market, "Net Amount")
CostAmount = get_market_setting(market, "Cost Amount")
qty = get_market_setting(market, "Quantity")
transNo = get_market_setting(market, "Transaction No.")
ItemNo = get_market_setting(market, "Item No.")
CustNo = "[Customer No_]"
ItemNr1 = "('80502842')"
Itemnr2 = ThisWorkbook.Sheets("Data").Range("C1").Value
ECPOS_arr = "(" & get_market_setting("LT", "ECPOS_Arr") & ")"
' SQL query string
myquery = "SELECT -SUM(" & NetAmount & ") AS [Result] FROM " & table_TSE & _
" WHERE " & ItemNo & " IN " & Itemnr2 & _
" AND [Date] BETWEEN '" & date_from & "' AND '" & date_to & "'"
' Set Recordset to query set database on SQL string
Set sourceRs = sourceDb.OpenRecordset(myquery)
'Assign Data to variable from RecordSet'
sourceRs.MoveFirst
NetAmount = sourceRs![Result]
sourceRs.Close
sourceDb.CloseRecordset
'Populate HFB Report file'
在此處輸入圖片說明
uj5u.com熱心網友回復：
如果 ItemNo 和 Itemnr2 總是有括號并且沒有單引號，則應將其視為字串：
Itemnr2 = ThisWorkbook.Sheets("Data").Range("C1").Value
myquery = "SELECT -SUM(" & NetAmount & ") AS [Result] FROM " & table_TSE & _
" WHERE '" & ItemNo & "' = '" & Itemnr2 & _
"' AND [Date] BETWEEN '" & date_from & "' AND '" & date_to & "'"
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/382710.html
下一篇：為每個對話框匯入CSV-VBA