Syntax of like statement using a variable

From: Brad Tornberg (Btorn_at_verizon.net)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 12:02:06 -0700

 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;"

Relevant Pages

  • Re: Cumulative sum in report
    ... FROM DrumWidth INNER JOIN ((([Core parts2] INNER JOIN Locations ON [Core ... My query uses a crosstab query called 'Core parts2' (bad naming practice, ... I had a really hard time working out a syntax for the DSum criteria of "field ...
    (microsoft.public.access.reports)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Implicit JOIN to Explicit with 3 non-nested tables
    ... This query with implicit JOIN works fine. ... INNER JOIN B ON B.ID = A.BID ... Access doesn't accept this syntax. ... JOIN equivalent of the implicit JOIN in the first query. ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)