I'm getting this error because try to put Itemnr2 variable as cell value. If I use plain value like in ItemNr1 all works. But I need to use value from cell because it will change time to time so don't want change code every time. How can I put value from cell to SQL code to get it correct?

It is working when I use it in other column where all values are numbers. In this column which I need there are values like b2b_pkl, and because of it I get those Conversion failed when converting the nvarchar value to data type int errors.

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'

enter image description here

CodePudding user response：

If ItemNo and Itemnr2 always have parentheses and no single quotes then should be treated as strings :

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 & "'"