VBA SQL - Missing right parenthesis error

[dbrother]

Hello All,

I've been plugging away at testing some VBA SQL that executes when a user clicks a button from a 'custom query' form. The user has lots of options as to what parameters should be included in the query, so there might be a better way to handle the syntax here. My main concern right now is just possible getting a fresh set of eyes on the VBA to see what I'm missing. I'm using the form's textbox field txtKitNum (strKit) to test the query, but no matter which textbox field I try to use, I get the right parenthesis missing error. Also, I'm using option groups to toggle query parameters, so if there is a better way to check the fields for nulls and modify the SQL accordingly, please put words to it.
Attached is a pic of the form if you're like me and are a visual person.
Thanks!

Here's the code:

strSQL = "SELECT ALL DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER, DRPROD.DR_SALES_ORDER_LINES.BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE PART#, "
strSQL = strSQL & "DRPROD.DR_SALABLE_PRODUCTS.PART_DESCRIPTION, "
strSQL = strSQL & "DRPROD.DR_SHIPMENT_PACKAGING.LOT_CODE, DRPROD.DR_SALES_ORDERS.BASE_ORDER_LINE_NUMBER, "
strSQL = strSQL & "DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER, DRPROD.DR_SHIPMENT_LINES.SHIP_DATE, "
strSQL = strSQL & "DRPROD.DR_SHIPMENTS.CARRIER_NAME, DRPROD.DR_CUSTOMER_GENERAL.CUSTOMER_NAME, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDERS.PURCHASE_ORDER_NUMBER "
strSQL = strSQL & "FROM DRPROD.DR_CUSTOMER_GENERAL, DRPROD.DR_SALES_ORDERS, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDER_LINES, DRPROD.DR_SHIPMENTS, DRPROD.DR_SHIPMENT_LINES, "
strSQL = strSQL & "DRPROD.DR_SHIPMENT_PACKAGING , DRPROD.DR_SALABLE_PRODUCTS "
strSQL = strSQL & "WHERE (DRPROD.DR_SALES_ORDER_LINES.FOB_PLANT_NUMBER='089' "
strSQL = strSQL & "AND DRPROD.DR_SHIPMENTS.FOB_PLANT_NUMBER='089' "


' ***  add SQL for each field in Serial Data option group
If optgrpSerial.Value = 1 Then
        If IsNull(txtSN.Value) Then
            'do nothing
        Else
            strSN = txtSN.Value
            strSQL = strSQL & "AND DRPROD.DR_SHIPMENT_PACKAGING.LOT_CODE LIKE '" & strSN & "' "
        End If
       
       
        If IsNull(txtKitNum.Value) Then
            'do nothing
        Else
            strKit = txtKitNum.Value
            strSQL = strSQL & "BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE LIKE '" & strKit & "') "
        End If
     
     
        If IsNull(cboSerialType.Value) Then
            'do nothing
        Else
            strSNType = cboSerialType.Value
            strSQL = strSQL & "AND DRPROD.DR_SHIPMENT_PACKAGING.LOT_CODE LIKE '" & strSNType & "' & '%' "
        End If

Else
'do nothing
End If



If optgrpSerial.Value = 1 And IsNull(strSN) And IsNull(strKit) And IsNull(strSNType) Then
    MsgBox "If you have the 'Serial Data' group enabled, you must enter or choose a value in at least one of the fields.", vbCritical, "NO VALUES ENTERED"
Else
' do nothing
End If
' *** end statments for Serial Data option group

' *** add SQL for each field in Date Rage option group
If optgrpDate.Value = 1 Then
    
    strFromDate = dteFrom.Value
    strToDate = dteTo.Value
    
    strSQL = strSQL & "AND DRPROD.DR_SHIPMENTS.SHIP_DATE >= '" & strFromDate & "'"
    strSQL = strSQL & "AND DRPROD.DR_SHIPMENTS.SHIP_DATE <= '" & strToDate & "'"
    Else
    'do nothing
End If
' *** end statements for Date Range option group

' *** add SQL for Order Data option Group

If optgrpOrder.Value = 1 Then
        If IsNull(txtCustPO.Value) Then
            'do nothing
        Else
            strCustPO = txtCustPO.Value
            strSQL = strSQL & "AND DRPROD.DR_SALES_ORDERS.PURCHASE_ORDER_NUMBER LIKE '" & strCustPO & "' "
        End If
        
        If IsNull(txtBaseNum.Value) Then
            'do nothing
        Else
            strBaseNum = txtBaseNum.Value
            strSQL = strSQL & "AND DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER LIKE '" & strBaseNum & "' "
        End If
Else
'do nothing
End If


If optgrpOrder.Value = 1 And IsNull(strCustPO) And IsNull(strBaseNum) Then
    MsgBox "If you have the 'Order Data' group enabled, you must enter or choose a value in at least one of the fields.", vbCritical, "NO VALUES ENTERED"
Else
' do nothing
End If

strSQL = strSQL & "AND ((DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER=DRPROD.DR_SALES_ORDER_LINES.ORDER_NUMBER) "
strSQL = strSQL & "AND (DRPROD.DR_SALES_ORDERS.SOLD_TO_NUMBER=DRPROD.DR_CUSTOMER_GENERAL.CUSTOMER_NUMBER) "
strSQL = strSQL & "AND (DRPROD.DR_SALES_ORDER_LINES.ORDER_NUMBER=DRPROD.DR_SHIPMENT_LINES.ORDER_NUMBER) "
strSQL = strSQL & "AND (DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER=DRPROD.DR_SHIPMENTS.SHIPMENT_NUMBER) "
strSQL = strSQL & "AND (DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER=DRPROD.DR_SHIPMENT_PACKAGING.SHIPMENT_NUMBER) "
strSQL = strSQL & "AND (DRPROD.DR_SALES_ORDER_LINES.ORDER_NUMBER=DRPROD.DR_SHIPMENT_PACKAGING.ORDER_NUMBER) "
strSQL = strSQL & "AND (DRPROD.DR_SALES_ORDER_LINES.PART_NUMBER=DRPROD.DR_SALABLE_PRODUCTS.PART_NUMBER)) "
strSQL = strSQL & "GROUP BY DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDER_LINES.BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE, "
strSQL = strSQL & "DRPROD.DR_SALABLE_PRODUCTS.PART_DESCRIPTION, "
strSQL = strSQL & "DRPROD.DR_SHIPMENT_PACKAGING.LOT_CODE, DRPROD.DR_SALES_ORDERS.BASE_ORDER_LINE_NUMBER, "
strSQL = strSQL & "DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER, DRPROD.DR_SHIPMENT_LINES.SHIP_DATE, "
strSQL = strSQL & "DRPROD.DR_SHIPMENTS.CARRIER_NAME, DRPROD.DR_CUSTOMER_GENERAL.CUSTOMER_NAME, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDERS.PURCHASE_ORDER_NUMBER "
strSQL = strSQL & "ORDER BY DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER ASC, "
strSQL = strSQL & "DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER Asc "


Set rst1 = con1.Execute(strSQL)

Any help/insight appreciated.
Thanks in advance,

dbrother

[Stewart Ross]

Hi dbrother. The SQL strings are not formed in a way that I recognise as valid, and I am sure that if you try to extract the core SQL from the string and run it in the query editor you will draw a complete blank. It is just not validly formed.

Firstly, you are using double vbar characters '||' liberally throughout your SQL strings, as in line 2 amongst many others:

strSQL = strSQL & "..., DR_SALES_ORDER_LINES.BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE PART#, "

These are not valid as separators in SQL strings. If field or table names contain spaces then each must be enclosed in brackets, like this:

[your table].[your field]

but the vbar is never used in Access SQL at all (or any other I know of).

Secondly, you are mixing single-quoted minus operator elements '-' in your SQL strings, which I cannot see will ever be considered valid in SQL, as in line 29:

strSQL = strSQL & "BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE LIKE '" & strKit & "') "

which also has a dangling right parenthesis at the end of it.

There is simply too much wrong all over the code for us to debug it for you, but a quick glance shows many systematic errors like the ones I have mentioned, and a detailed look may show many more.

Use the compiler to assist you when you write code like this - but even if it compiles it may not be valid SQL and therefore fail at run-time.

-Stewart

[dbrother]

Thanks for the quick reply. The vbar is valid in SQL and in Access as I have other queries in other programs that work successfully. The vbar indicates a concatenation. In this example it uses 3 fields seperated by '-' to equal another field in another table. I feel very strongly that this isn't the problem, because I have used it in the past and the compiler runs fine.

The second point you make about the dangling parenthesis was my attempt at solving the 'missing parenthesis error'. The test that I'm using hits on that line of code. If you look at the form, I'm using only the strKit value, which would hit at that line of code, which would also mean that it's the end of that part of the SQL statement.

The query below works fine. I made a test pass through query to test what you say about the vbar. Pic attached.

SELECT ALL DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER AS BASE_NUM, DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER AS REF_NUM, 
DRPROD.DR_SALES_ORDER_LINES.BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE AS PART_NUM, 
DRPROD.DR_SALABLE_PRODUCTS.PART_DESCRIPTION AS DESCRIPTION, DRPROD.DR_SHIPMENT_PACKAGING.LOT_CODE AS SERIAL_NUM, 
DRPROD.DR_SALES_ORDERS.BASE_ORDER_LINE_NUMBER AS PO_LINE_NUM, DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER AS BOL_NUM, 
DRPROD.DR_SHIPMENT_LINES.SHIP_DATE, DRPROD.DR_SHIPMENTS.CARRIER_NAME AS CARRIER, 
DRPROD.DR_CUSTOMER_GENERAL.CUSTOMER_NAME AS CUSTOMER, DRPROD.DR_SALES_ORDERS.PURCHASE_ORDER_NUMBER AS PO_NUM, 
DRPROD.DR_SHIPMENT_LINES.SHIPMENT_QTY AS SHIP_QTY
FROM DRPROD.DR_CUSTOMER_GENERAL, DRPROD.DR_SALES_ORDERS, 
DRPROD.DR_SALES_ORDER_LINES, DRPROD.DR_SHIPMENTS, DRPROD.DR_SHIPMENT_LINES, 
DRPROD.DR_SHIPMENT_PACKAGING, DRPROD.DR_SALABLE_PRODUCTS
WHERE (DRPROD.DR_SALES_ORDER_LINES.FOB_PLANT_NUMBER='089'
 AND DRPROD.DR_SHIPMENTS.FOB_PLANT_NUMBER='089'
 AND DRPROD.DR_SHIPMENTS.SHIP_DATE>=SYSDATE-60
 AND DRPROD.DR_SALES_ORDER_LINES.BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE='X009225-016-610')
 AND  ((DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER=DRPROD.DR_SALES_ORDER_LINES.ORDER_NUMBER)
 AND (DRPROD.DR_SALES_ORDERS.SOLD_TO_NUMBER=DRPROD.DR_CUSTOMER_GENERAL.CUSTOMER_NUMBER)
 AND (DRPROD.DR_SALES_ORDER_LINES.ORDER_NUMBER=DRPROD.DR_SHIPMENT_LINES.ORDER_NUMBER)
 AND (DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER=DRPROD.DR_SHIPMENTS.SHIPMENT_NUMBER)
 AND (DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER=DRPROD.DR_SHIPMENT_PACKAGING.SHIPMENT_NUMBER)
 AND (DRPROD.DR_SALES_ORDER_LINES.ORDER_NUMBER=DRPROD.DR_SHIPMENT_PACKAGING.ORDER_NUMBER)
 AND (DRPROD.DR_SALES_ORDER_LINES.PART_NUMBER=DRPROD.DR_SALABLE_PRODUCTS.PART_NUMBER))
GROUP BY DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER, DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER, 
DRPROD.DR_SALES_ORDER_LINES.BASE_PART||'-'||DRPROD.DR_SALES_ORDER_LINES.PAINT_CODE||'-'||DRPROD.DR_SALES_ORDER_LINES.PACK_CODE, 
DRPROD.DR_SALABLE_PRODUCTS.PART_DESCRIPTION, DRPROD.DR_SHIPMENT_PACKAGING.LOT_CODE, 
DRPROD.DR_SALES_ORDERS.BASE_ORDER_LINE_NUMBER, DRPROD.DR_SHIPMENT_LINES.SHIPMENT_NUMBER, 
DRPROD.DR_SHIPMENT_LINES.SHIP_DATE, DRPROD.DR_SHIPMENTS.CARRIER_NAME, 
DRPROD.DR_CUSTOMER_GENERAL.CUSTOMER_NAME, DRPROD.DR_SALES_ORDERS.PURCHASE_ORDER_NUMBER, 
DRPROD.DR_SHIPMENT_LINES.SHIPMENT_QTY
ORDER BY DRPROD.DR_SALES_ORDERS.BASE_ORDER_NUMBER ASC, 
DRPROD.DR_SALES_ORDERS.SALES_ORDER_NUMBER ASC

[FishVal]

Left parentheses in WHERE clause is being completed with right one only when txtKitNum.Value is NULL.

[Stewart Ross]

Glad Fish has found where your left parenthesis is missing.

Re the vbar - you mention that it works in a pass-through query. You did not mention before that this was the case.

Pass-through queries do not use native Access SQL syntax, as they are passed to a proprietary server for interpretation. Their definition in the Access help clarifies this:

pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data.

In Access itself I can be quite certain the vbar is not a concatenation operator, and attempting to do what you can do in your pass-through query on an Access table results in an error message stating that the use of the vbar is invalid.

You are actually using PL/SQL on an Oracle database from an Access front end, using pass-through queries to execute the PL/SQL on the Oracle back-end. In PL/SQL the || operator is indeed concatenation.

As this is an Access forum you may see why not telling us about it can be confusing!

-Stewart