Syntax of like statement using a variable

[Brad Tornberg]

I need to pass a variable value into the select query and
when I try all different types of syntax it either gives
me invalid column name on the mystr variable or returns 0
records. When I do this in access and paste to a query it
works fine for both of these statements but not when I
code SQL (ADO)

This one works:

' WORKS!!! Strsql = "SELECT SFORDFIL_SQL.item_no, Count
(SFORDFIL_SQL.qty_complete) AS CountOfqty_complete,
SFORDFIL_SQL.ord_status, SFORDFIL_SQL.ord_type,
SFORDFIL_SQL.issue_loc, SFORDFIL_SQL.compl_dt,
SFDTLFIL_SQL.qty, SFDTLFIL_SQL.qty_issued,
SFDTLFIL_SQL.comp_item_no, IMITMIDX_SQL.user_amt FROM
(SFORDFIL_SQL INNER JOIN SFDTLFIL_SQL ON
SFORDFIL_SQL.ord_no = SFDTLFIL_SQL.ord_no) INNER JOIN
IMITMIDX_SQL ON SFDTLFIL_SQL.item_no =
IMITMIDX_SQL.item_no GROUP BY SFORDFIL_SQL.item_no,
SFORDFIL_SQL.ord_status, SFORDFIL_SQL.ord_type,
SFORDFIL_SQL.issue_loc, SFORDFIL_SQL.compl_dt,
SFDTLFIL_SQL.qty, SFDTLFIL_SQL.qty_issued,
SFDTLFIL_SQL.comp_item_no, IMITMIDX_SQL.user_amt HAVING
(((SFDTLFIL_SQL.comp_item_no) Like 'M%'))ORDER BY
SFORDFIL_SQL.compl_dt DESC;"


But this one doesn't (look at the LIKE statement for
differences):

Strsql = "SELECT SFORDFIL_SQL.item_no,
SFORDFIL_SQL.ord_status, SFORDFIL_SQL.ord_type,
SFORDFIL_SQL.issue_loc, SFORDFIL_SQL.compl_dt,
SFDTLFIL_SQL.qty, SFDTLFIL_SQL.qty_issued,
SFDTLFIL_SQL.comp_item_no, IMITMIDX_SQL.user_amt FROM
(SFORDFIL_SQL INNER JOIN SFDTLFIL_SQL ON
SFORDFIL_SQL.ord_no = SFDTLFIL_SQL.ord_no) INNER JOIN
IMITMIDX_SQL ON SFDTLFIL_SQL.item_no =
IMITMIDX_SQL.item_no GROUP BY SFORDFIL_SQL.item_no,
SFORDFIL_SQL.ord_status, SFORDFIL_SQL.ord_type,
SFORDFIL_SQL.issue_loc, SFORDFIL_SQL.compl_dt,
SFDTLFIL_SQL.qty, SFDTLFIL_SQL.qty_issued,
SFDTLFIL_SQL.comp_item_no, IMITMIDX_SQL.user_amt HAVING
(((SFORDFIL_SQL.Item_No) Like ' & Chr(34) & [mystr2] & Chr
(34) & ') And ((SFDTLFIL_SQL.comp_item_no) Like 'M%'))
ORDER BY SFORDFIL_SQL.compl_dt DESC;"

 

[Cheval]

This part is wrong.
"Like ' & Chr(34) & [mystr2] & Chr(34) & ')"
You can't do that.

I still practice the idea of using a global variable
returned via function in the query. It's fast and easily
works every time.

Simplifed version example:
"Select * from tblTable Where CustID = Get_CustID();"
--Module--
Dim lngCustID As Long
Public Function Get_CustID() as Long
Get_CustID = lngCustID
End Function

Actual version I use is:
"Select * from tblTable Where CustID = Get_Global(3);"
Where it looks up the variable in the global variable
array.

If you want more info on this then send a post to the
replication group.