Microsoft Query SQL- User Defined Where Parameters

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I have used Microsoft Query to create a Query to return data to excel.
It contains 3 tables that are joined by 3 key fields. Up to that point in the SQL Statement, it works fine.
Then for the Where clause I used Criteria where 3 different fields have the ability to choose up to five different filters based on user defined criteria.
Originally when I 1st wrote it I typed in the Or statements all on one line. When I refreshed the data or returned the data to excel, and input various criteria as a test, it worked. At this point I could be okay with that, because even when in Excel, if I change the criteria and right click the data and select Refresh it still works. However, I have to validate my query, and if you go back in to edit the query and refresh it or return data to excel again it gives a ORA-00936: MISSING EXPRESSION ERROR.
I found some solutions; one of which is to change the Where criteria from having brackets to a simple ?. This creates a hardship because then the user input prompts don't let you know what it's prompting you for.
But when I add the brackets back into it it gives me the ORA-00936 error message again.
Does anyone know a way around this?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello,

I you use parameters in your query then you have usually 3 choices:

to have a static value
to have a prompt box
or to use some cells


Meaning you could have some cells with a heading ie:
Meaning in A1 typing Input Name
User will input in A2
in A3 typing Input Age
in A4 user will input Age....


So if you define parameters in your query using [name] [age] then what you can do is :

Go to the query result right click: Select parameters:

Then you will see a window prompting you to choose where you want your parameters to come from. You will do this for each of them:

select the last option [get the value from the following cell] selecting the cell for input and tick refresh when data change.

The input cell should be place may be on another sheet or on the first row horizontally.

If you want the prompt personalized and then you can typing what you want as prompt by selecting the first option and changing the prompt string.
If you have several parameter think to select each one of them and reenter the prompt.
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
thanks, but I am well aware of how to do that.
I probably didn't explain well enough.
After successfully creating a query (using Microsoft Query in Excel) and returning the results successfully as well, I am having to validate the query that I created. To validate it I have to be able to go back in to edit the query. When I do this, with or without changes to the query, if I then refresh the query or try and return the results back to Excel, I get a ORA-00936: missing expression error.
Here is an example of the criteria I write in the beginning that is successful in returning the DB results to excel:
TEMPLATEProductFamily-ReportedCode.xls
ABCDEFGH
1PRODUCT_FAMILYREPORTED_DEVICE_CODEDIVISIONENTRY_DATEREPORTED_DEVICE_CLARIFICATIONREGIONNAME(COUNTRY)COMPLAINT
2Like[PF1]OrLike[PF2]OrLike[PF3]OrLike[PF4]OrLike[PF5]NotLike[Exclude]'CP'>=[Date1]And<=[Date2]Like[CL1]OrLike[CL2]OrLike[CL3]OrLike[CL4]OrLike[CL5]Like[R1]Like[C1]OrLike[C2]OrLike[C3]OrLike[C4]OrLike[C5]Like[Y/N]
Query Criteria


Now, at the point of writing the above criteria in MS Query, if I just refresh the data without returning it to Excel, it is successful, and at this point I've selected to view the SQL which here is a copy/paste of that:

Code:
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION
FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES
WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE>=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?))

So, I copied/pasted that SQL into a new worksheet as text. Went out of MS Query, opened a new Workbook in Excel, went straight to Data, Import External Data, New Database Query, then opened the View SQL and pasted the SQL (from above) and end up getting a Syntax error or access violation error box.

I really need help with the MS Query portion of this Message.

Recap:
1. No problem writing initial Query, Parameters, Fields, Tables, Joins, Criteria--nothing. Everything runs smoothly and returns data from DB into Excel exactly as needed.
2. Problem arises when having to edit the query. When you go back in to it, after a query refresh, or an attempt to return data to Excel a ORA-00936: missing expression error message pops up.

3. Only way I've been able to fix it is by re-writing the Criteria (which i've copied/pasted an example above) everytime before returning data to Excel
:rolleyes: :rolleyes: :confused: :eek: :rolleyes: :rolleyes:
PLEASE HELP!!!, I'm very frustrated at validation processes at this point. it's the only thing holding me back from finalizing a major project.

Thanks,

David
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello there,

I enter ORA-00936 in Google and find out that you are probably not the only one experimenting this error.

Oracle site says:

ORA-00936 missing expression

Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

Action: Check the statement syntax and specify the missing component


If you do the same you may find that a few of them seems to have a problem with dates.

So what I will suggest to see if your problem is something to do with dates is to remove the part of your query:

Code:
AND (A_COMPL_SUMMARY.ENTRY_DATE>=? And A_COMPL_SUMMARY.ENTRY_DATE<=?)

Run your query again

If you don't get the error anymore then you may want to check [maybe doing a query directly in Oracle] which kind of format Oracle keep its date into and check that it corresponds to yours and as well see if you have the same time system on the server[where oracle database is] and on the Excel user].


What you can do as well is to enter 2 static dates instead of parameter trying diverse format to see which one is accepted.



This is just some ideas as I am not familiar with Oracle.

As well what are the people inputting in the yes/no field. Is it 0 or 1???

It is hard to come up with a solution but may be via suggestion you will come up with it.

Can you access Oracle directly and check if you have any problem with your query syntax.
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438

ADVERTISEMENT

Yeah, I googled the error message as well and thought it might be the date as well. I've actually gone 1 criteria at a time trying to pinpoint where the problem might lie. Well, at this point I have an appointment to meet with an Oracle DB rep to see what they think. It might end up just being a bug that is not allowing Excel to query properly with the syntax as it is.
I guess I just have to wait until then. I'll definitely post what I find all over the place though, considering how many finds google came up with on that error message code.
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello again,

Last attempt to resolve your problem then we will leave it to the EXPERTS [oracle].

I have been looking for query in oracle to see how they differ:

select achat from biblio
where achat between '01-jan-88' and '31-dec-88'

So if we use logic, if you pass a date as string it should work.

So if you convert the parameter to string it COULD work.

So do you want to try as follow:
My sample

SELECT Assets.Description, str(purchase_date)
FROM `D:\FanfanDocuments\access\assetstracking2000P`.Assets Assets
WHERE (str(purchase_date)>=? And str(purchase_date)<=?)

What I did is used str function to convert the date to a string as in Access if records are returned their are returned as day and time not just day.

It could be just that or may be not but it could be worth trying as this doesn't take much effort.

Please let me know when you get it working what was the solution
 

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Sorry I paste the wrong code:

SELECT Assets.Description, str(purchase_date)
FROM `D:\FanfanDocuments\access\assetstracking2000P`.Assets Assets
WHERE (Assets.Purchase_Date>=? And Assets.Purchase_Date<=?)

only converting the return column

or you could LAST TRY promised:

SELECT Assets.Description, Assets.Purchase_Date
FROM `D:\FanfanDocuments\access\assetstracking2000P`.Assets Assets
WHERE (Assets.Purchase_Date>=str(?) And Assets.Purchase_Date<=str(?))
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
Unfortunately, I've already tried the query by eliminating the date, and still got the same problem. So even if I try this, it won't matter. I believe there's just some type of "bug" (from what I read on Oracle forums) in regards to SQL in Excel trying access Oracle DB.
I'm going to be visiting our Oracle rep next week (early in the week). So I will post someting when I find the solution.
 

Forum statistics

Threads
1,136,370
Messages
5,675,369
Members
419,567
Latest member
Naveen27mech

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top