revamped code still returns 0 records

[Btorn]

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

 

[John Vinson]

((SFDTLFIL_SQL.comp_item_no) Like 'M%'))
ORDER BY SFORDFIL_SQL.compl_dt DESC;"

Change the % to *.

 

[Dan Artuso]

Hi,
What you need to do is Debug.print your strSql variable and then examine it.
Copy and paste it into the query designer in SQL view and see if it returns what you think it should.

HTH
Dan Artuso, MVP

 

[Brad Tornberg]

Actually either one works. The problem is when I try to
use a variable mystr1 as a comparison. It returns 0
records. If I use the one you suggested it works well but
when I add the second like statement it doesn;t i.e"



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

 

[Brad Tornberg]

I did and it works fine in access. Is it syntax using the
variable? When I remove that part it works fine in VBA.
Can you examine my syntax to see if I'm doing something
wrong?

-----Original Message-----
Hi,
What you need to do is Debug.print your strSql variable and then examine it.
Copy and paste it into the query designer in SQL view and

see if it returns what you think it should.

HTH
Dan Artuso, MVP


"Btorn" <[email protected]> wrote in

message news:[email protected]...

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

.

 

[Dan Artuso]

Hi,
Are we talking about the same thing here?
You've put this line in your code:
Debug.Print Strsql

Then you've copied the result from the immediate window and pasted it
into SQL view of the query designer?
Can you post the sql that appears in the immediate window?

Dan Artuso, MVP

I did and it works fine in access. Is it syntax using the
variable? When I remove that part it works fine in VBA.
Can you examine my syntax to see if I'm doing something
wrong?

-----Original Message-----
Hi,
What you need to do is Debug.print your strSql variable and then examine it.
Copy and paste it into the query designer in SQL view and

see if it returns what you think it should.

HTH
Dan Artuso, MVP


"Btorn" <[email protected]> wrote in

message news:[email protected]...

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

.