Multiple JOINS cause ERROR: 3141 1

[tbonehwd]

I have tracked down my problem I was previously having being caused by multiple JOINs, could someone please give a direction to look in.

strSQL = "SELECT PROOLN_M.ITM_NUM, PROOLN_M.DSC_001, PROOLN_M.DSC_003, Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_PUR " & _
", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_RET " & _
", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_PUR " & _
", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_RET " & _
"FROM PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM " & _
"WHERE PROOLN_M.OLN_STA = 'Z' " & _
"AND PROOLN_M.SHP_CTM = '000000601248'
"AND PROORD_M.ACT_DTE >=#06/30/2005#
"AND PROORD_M.ACT_DTE <=#07/31/2005#
"AND PROOLN_M.ITM_NUM = '0006'
"GROUP BY PROOLN_M.ITM_NUM, PROOLN_M.DSC_001, PROOLN_M.DSC_003;"

When I do this it works fine, however I want to add one more field to my where clause CDSITM_M.ITM_SAC which is located in a table called CDSITM_M

When I change the above FROM to be

"(FROM PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM=CDSITM_M.ITM_NUM " & _

I get Error:3141
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I have done multiple joins in the past with Access and have placed the () the same way and they have worked before...

I am stumped!

Thanks,

Tbonehwd

 

[PHV]

Replace this:
"(FROM PROOLN_M INNER JOIN
By this:
"FROM (PROOLN_M INNER JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tbonehwd]

OMG I cannot believe I wasted so much time not really looking at my old code example.


Your the BEST!!!!!