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
4 5379
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
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
-
Left parentheses in WHERE clause is being completed with right one only when txtKitNum.Value is NULL.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Phil Powell |
last post by:
<input type="submit" name="delete_student" value="Delete Applicant"
onClick="setSubmitVal(this); return
willDeleteApplicant('O&#039;Connor, Kerry B');">
This HTML tag causes Javascript errors in...
|
by: bazubwabo |
last post by:
hi everybody,
by creating my employee table ,i've gotten a missing right parenthesis error but i don't know why.Could please help me to find out the error.
SQL> CREATE TABLE EMPLOYEE
2 ...
|
by: kuzen |
last post by:
Hi guys,
Could you plz check this code out and tell what the problem is.
Thanks in advance
CREATE TABLE RESERVATIONS (
RESERVATION_ID NUMBER(5),
RENTER NUMBER(6),
CAR_TYPE NUMBER(1),...
|
by: isaacr |
last post by:
hi everyone. im new here and it looks like a good place to get some help with school. basically im currently trying to write a program with c++ on a program called Turbo C_5 (i think). what i am...
|
by: keokepa |
last post by:
hi, i got this missing right parenthesis error while trying to create a table
and i'm not too sure where i went wrong
could someone point out my mistakes?
this is what i had typed into Oracle...
|
by: Thakur vikas |
last post by:
when we run the query then we got
": (Error): ORA-00907: missing right parenthesis"
what may be the error?
|
by: sugumar1985 |
last post by:
select * from test where sal in ( case sal
when (sal < 5000) and (sal > 2000) then 2000
else 3000
end)
SQL> /
when (sal<5000 and sal>2000) then 2000
*
ERROR at line 2:
ORA-00907:...
|
by: karlectomy |
last post by:
Hi all,
I'm trying to run this table creation script but I run into the ORA-00907 error: missing right parenthesis. Perhaps someone might know where I am going wrong?
CREATE TABLE...
|
by: ozarka |
last post by:
This select statement works and gives me True/False in RC33 column however the second statement gives me following error. Any idea?
odbc--call failed.
ora-00907: missing right parenthesis
-...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |