revamped code still returns 0 records

  • Thread starter Thread starter Btorn
  • Start date Start date
B

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;"
 
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
 
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;"
 
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
Click to expand...
see if it returns what you think it should.
HTH
Dan Artuso, MVP


"Btorn" <[email protected]> wrote in
Click to expand...
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;"
Click to expand...


.
Click to expand...
 
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


Brad Tornberg said:
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
Click to expand...
see if it returns what you think it should.
HTH
Dan Artuso, MVP


"Btorn" <[email protected]> wrote in
Click to expand...
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;"
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top