SQL CODE WORKS IN ACCESS BUT NOT IN VBA

[Btorn]

I have the following SQL which when pasted into a query
works fine. When i try to execute this in VBA it returns
no records. Help! Is it syntax or is their another way to
get the same result. The trouble is the "like" statement
but even when i remove it 0 records are returned (except
in the query not in vba). I know the [] aren't recognized
in SQL but there must be a way to use a variable name:

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
(((SFORDFIL_SQL.Item_No) Like [mystr1]) And
((SFDTLFIL_SQL.comp_item_no) Like 'M*'))ORDER BY
SFORDFIL_SQL.compl_dt DESC;

 

[Douglas J. Steele]

What is [mystr1] in (((SFORDFIL_SQL.Item_No) Like [mystr1])? If it's a
variable, you need to use
"...(((SFORDFIL_SQL.Item_No) Like " & Chr(34) & [mystr1] & Chr(34) & ")..."

(Chr(34) is ", so that you have quotes around your text fields. If Item_No
is numeric, you don't need Chr(34), but there's no point using Like, as Like
only works with Text)

Also, how are you using that SQL statement in code? If you're using ADO, the
wildcard symbol is %, not *.

 

[Btorn]

I have sent you over a new code but does this mean I can
not use [mystr1] if it is defined as a string?

-----Original Message-----
What is [mystr1] in (((SFORDFIL_SQL.Item_No) Like [mystr1])? If it's a
variable, you need to use
"...(((SFORDFIL_SQL.Item_No) Like " & Chr(34) & [mystr1] & Chr(34) & ")..."

(Chr(34) is ", so that you have quotes around your text fields. If Item_No
is numeric, you don't need Chr(34), but there's no point using Like, as Like
only works with Text)

Also, how are you using that SQL statement in code? If you're using ADO, the
wildcard symbol is %, not *.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have the following SQL which when pasted into a query
works fine. When i try to execute this in VBA it returns
no records. Help! Is it syntax or is their another way to
get the same result. The trouble is the "like" statement
but even when i remove it 0 records are returned (except
in the query not in vba). I know the [] aren't recognized
in SQL but there must be a way to use a variable name:

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
(((SFORDFIL_SQL.Item_No) Like [mystr1]) And
((SFDTLFIL_SQL.comp_item_no) Like 'M*'))ORDER BY
SFORDFIL_SQL.compl_dt DESC;

.

 

[John Vinson]

I have sent you over a new code but does this mean I can
not use [mystr1] if it is defined as a string?

VBA is one language, SQL is a different one! SQL queries have no
knowledge of or access to VBA variables. As suggested, you need to
concatenate the value of the variable into your SQL string.