The following line was defined in separate header file

Public Const tbl_mas_product_line = "IM_ProductLine"

Only join I could find in my stuff. But notice in the first select statement I use the syntax tbl_mas_product_line & ".ProductLine" ....which with the definition above equals: IM_ProductLine.ProductLine

Your lexical error could just be you need the table object and field ref syntax (eg; table.field) which you don't reference in your select statement

sql_1 = "SELECT DISTINCT " & tbl_mas_product_line & ".ProductLine," & tbl_mas_product_line & ".ProductLineDesc," & tbl_mas_product_line & ".InventoryAcctKey," & _
                       tbl_mas_product_line & ".CostOfGoodsSoldAcctKey," & tbl_mas_product_line & ".SalesIncomeAcctKey," & tbl_mas_product_line & ".ReturnsAcctKey," & _
                       tbl_mas_product_line & ".AdjustmentAcctKey," & tbl_mas_product_line & ".PurchaseAcctKey," & tbl_mas_product_line & ".PurchaseOrderVarianceAcctKey," & _
                       tbl_mas_product_line & ".ManufacturingVarianceAcctKey "
                      
        sql_2 = "FROM {oj " & tbl_mas_ci_item & " JOIN " & tbl_mas_product_line & _
                " ON " & tbl_mas_ci_item & ".ProductLine = " & tbl_mas_product_line & ".ProductLine}" & _
                " WHERE (((" & tbl_mas_ci_item & ".PrimaryVendorNo)='" & mcr.vendor_id & "'))"

        sql_statement = sql_1 & sql_2

3 issues, 2 of which you can fix:

1) Good points from sevendogzero about prefixing with an alias. You need to do that here (and there are a few ways to do that).

SELECT A.ItemCode, B.BillNo, B.ComponentItemCode, B.QuantityPerBill

Otherwise it's too ambiguous for ProvideX ODBC (used by Sage 100 Std and Advanced)

2. Change LEFT INNER JOIN to LEFT OUTER JOIN (assuming you want a Left Join)

3. The "Expected Lexical Element" error occurs because ProvideX ODBC doesn't support the subquery / nested SELECT