MS Access VBA: Syntax error with inner join

Question

I am attempting to add a simple inner join to a MS Access query. I am trying to connect a table with a single field simply to restrict results to a subset of cusnums. I am getting a Syntax error (See below) that I simply do not understand.

I included the old code and the new code below. The only change is the addition of the inner join. Any help would be appreciated. Thanks.

Error Message:

Syntax error (missing operator) in query expression

'(qry_Co_1_PRET_Routed_2_Days_Out.cusnum = tbl_Store_With_Sat_Ord.shpcusnum)
LEFT JOIN 
        qry_Co_1_PRET_Open_Order_2_Days_Out 
ON      
        (qry_Co_1_PRET_Routed_2_Days_Out.co_num = qry_Co_1_PRET_Open_Order_2_Days_Out.co_num) 
    AND 
        (qry_Co_1_PRET_Routed_2_Days_Out.cusnum = qry_Co_1_PRET_Open_Order_2_Days_Out.shpcusnum'

Old Code:

SELECT 
        IIf([totordqty]>0, "Order In","No Order") AS OO_Staus, 
        qry_Co_1_PRET_Routed_2_Days_Out.co_num, 
        qry_Co_1_PRET_Routed_2_Days_Out.prislscod, 
        qry_Co_1_PRET_Routed_2_Days_Out.cusnum, 
        qry_Co_1_PRET_Routed_2_Days_Out.co_nam, 
        qry_Co_1_PRET_Routed_2_Days_Out.cusphn, 
        Switch([totordqty]>0,"",[cusphn]<>"",[cusphn],True,"No Phone # on File") AS Cust_Phone, 
        qry_Co_1_PRET_Routed_2_Days_Out.rtedow, 
        qry_Co_1_PRET_Routed_2_Days_Out.rtecod, 
        qry_Co_1_PRET_Routed_2_Days_Out.stpnum, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.plnshpdat, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.totordqty, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.ordnetwgt, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.ordcub, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.shptotamt, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.prcflg, 
        qry_Co_1_PRET_Routed_2_Days_Out.cusemaadd, 
        qry_Co_1_PRET_Routed_2_Days_Out.TD_Email
FROM 
        qry_Co_1_PRET_Routed_2_Days_Out 
LEFT JOIN 
        qry_Co_1_PRET_Open_Order_2_Days_Out 
ON 
        (qry_Co_1_PRET_Routed_2_Days_Out.co_num = qry_Co_1_PRET_Open_Order_2_Days_Out.co_num) 
    AND 
        (qry_Co_1_PRET_Routed_2_Days_Out.cusnum = qry_Co_1_PRET_Open_Order_2_Days_Out.shpcusnum)
WHERE 
        (((IIf([totordqty]>0,"Order In","No Order"))="No Order"))
ORDER BY 
        IIf([totordqty]>0,"Order In","No Order"), 
        qry_Co_1_PRET_Routed_2_Days_Out.rtecod, 
        qry_Co_1_PRET_Routed_2_Days_Out.stpnum;

New Code:

SELECT 
        IIf([totordqty]>0,"Order In","No Order") AS OO_Staus, 
        qry_Co_1_PRET_Routed_2_Days_Out.co_num, 
        qry_Co_1_PRET_Routed_2_Days_Out.prislscod, 
        qry_Co_1_PRET_Routed_2_Days_Out.cusnum, 
        qry_Co_1_PRET_Routed_2_Days_Out.co_nam, 
        qry_Co_1_PRET_Routed_2_Days_Out.cusphn, 
        Switch([totordqty]>0,"",[cusphn]<>"",[cusphn],True,"No Phone # on File") AS Cust_Phone, 
        qry_Co_1_PRET_Routed_2_Days_Out.rtedow, 
        qry_Co_1_PRET_Routed_2_Days_Out.rtecod, 
        qry_Co_1_PRET_Routed_2_Days_Out.stpnum, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.plnshpdat, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.totordqty, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.ordnetwgt, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.ordcub, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.shptotamt, 
        qry_Co_1_PRET_Open_Order_2_Days_Out.prcflg, 
        qry_Co_1_PRET_Routed_2_Days_Out.cusemaadd, 
        qry_Co_1_PRET_Routed_2_Days_Out.TD_Email
FROM 
        qry_Co_1_PRET_Routed_2_Days_Out 
INNER JOIN
        tbl_Store_With_Sat_Ord
ON
        (qry_Co_1_PRET_Routed_2_Days_Out.cusnum = tbl_Store_With_Sat_Ord.shpcusnum)
LEFT JOIN 
        qry_Co_1_PRET_Open_Order_2_Days_Out 
ON      
        (qry_Co_1_PRET_Routed_2_Days_Out.co_num = qry_Co_1_PRET_Open_Order_2_Days_Out.co_num) 
    AND 
        (qry_Co_1_PRET_Routed_2_Days_Out.cusnum = qry_Co_1_PRET_Open_Order_2_Days_Out.shpcusnum)
WHERE 
        (((IIf([totordqty]>0,"Order In","No Order"))="No Order"))
ORDER BY 
        IIf([totordqty]>0,"Order In","No Order"), 
        qry_Co_1_PRET_Routed_2_Days_Out.rtecod, 
        qry_Co_1_PRET_Routed_2_Days_Out.stpnum;

Access requires parentheses in the FROM clause for queries which include more than one join. https://stackoverflow.com/questions/20929332/multiple-inner-join-sql-access/20929533#20929533 — HansUp, Mar 22 '18 at 17:10

score 1 · Accepted Answer · answered Mar 22 '18 at 18:00

Change the FROM section onwards of your SQL code to:

FROM 
(
            qry_Co_1_PRET_Routed_2_Days_Out 
    LEFT JOIN 
            qry_Co_1_PRET_Open_Order_2_Days_Out 
    ON      
            (qry_Co_1_PRET_Routed_2_Days_Out.co_num = qry_Co_1_PRET_Open_Order_2_Days_Out.co_num) 
        AND 
            (qry_Co_1_PRET_Routed_2_Days_Out.cusnum = qry_Co_1_PRET_Open_Order_2_Days_Out.shpcusnum)
)
INNER JOIN
        tbl_Store_With_Sat_Ord
ON
        (qry_Co_1_PRET_Routed_2_Days_Out.cusnum = tbl_Store_With_Sat_Ord.shpcusnum)
WHERE 
        [totordqty] <= 0
ORDER BY 
        [totordqty] <= 0, 
        qry_Co_1_PRET_Routed_2_Days_Out.rtecod, 
        qry_Co_1_PRET_Routed_2_Days_Out.stpnum;

Note the changes to the WHERE & ORDER BY clauses.

This answered my question, thank you. A note however: changing the WHERE and ORDER BY clauses caused it to return no records. I changed it back to the original and I am now getting the correct results. — Joshu's Mu, Mar 22 '18 at 18:19
@joshusmu That's odd given that the logic is obviously the same. I can only assume there may be null values, hence the `WHERE` clause may need to be `[totordqty] <= 0 OR [totordqty] IS NULL`. Just a guess however without seeing the data. — Lee Mac, Mar 22 '18 at 18:30

MS Access VBA: Syntax error with inner join

1 Answers1