MSQuery is forcing changes to SQL string 1

[JSouthern]

I am using an ODBC link with MSQuery to download data from Sage Line 100 to Excel.
I type "GUM" or "FLM" or "GEN" in the first row of the MSQuery grid, (followed by other criteria in next column)
and this works. the SQL string is

SELECT WMORDER.WM_ACCOUNT, WMORDER.WM_DATE_RECV,WMORDER.WM_CUST_REF,WMORDER.WM_ORDER_NO, WMTRANS.WT_BATCH_DATE, WMTRANS.WT_DELIVERY_DATE,WMTRANS.WT_JOB_CODE, WMTRANS.WT_JOB_DESC, WMTRANS.WT_QUANTITY, WMTRANS.WT_PRODUCT, WMTRANS.WT_LENGTH, WMTRANS.WT_WIDTH, WMTRANS.'WT_COLOUR_CODE#1', WMTRANS.WT_COLOR_CODE#2'WMORDER.WM_STATUS

FROM WINDMILL.WMORDER WMORDER, WINDMILL.WMTRANS WMTRANS

WHERE WMORDER.THIS_RECORD=WMTRANS.PARENT_RECORD

AND ((WMTRANS.WT_NOMCC="GUM" OR WMTRANS.WT_NOMCC="FLM" OR WMTRANS.WT_NOMCC="GEN")
AND(WMTRANS.WT_PRINT="R") AND (WMTRANS.WT_PRODUCT<>"GW90") AND (WMORDER.WT_STATUS Between 8 And 10))

When I refresh the query however i get unexpected results. The MSQuery grid has changed to:-
"GUM"
or"FLM"
or "GEN" on different rows with other criteria following on .
the SQL string is now

SELECT WMORDER.WM_ACCOUNT, WMORDER.WM_DATE_RECV,WMORDER.WM_CUST_REF,WMORDER.WM_ORDER_NO, WMTRANS.WT_BATCH_DATE, WMTRANS.WT_DELIVERY_DATE,WMTRANS.WT_JOB_CODE, WMTRANS.WT_JOB_DESC, WMTRANS.WT_QUANTITY, WMTRANS.WT_PRODUCT, WMTRANS.WT_LENGTH, WMTRANS.WT_WIDTH, WMTRANS.'WT_COLOUR_CODE#1', WMTRANS.WT_COLOR_CODE#2', WMORDER.WM_STATUS

FROM WINDMILL.WMORDER WMORDER, WINDMILL.WMTRANS WMTRANS

WHERE WMORDER.THIS_RECORD=WMTRANS.PARENT_RECORD
AND (( WMTRANS.WTNOMCC="GUM") AND (WMTRANS.WT_PRINT = "R")AND WMTRANS.WT_PRODUCT<>"GW90")AND WMORDER.WM_STATUS Between 8 And 10)
OR (WMTRANS.WT_NOMCC="FLM") AND (WMTRANS.WT_PRINT="R") AND WMTRANS.WT_PRODUCT<>"GW90" AND WMORDER.WM_STATUS Between 8 And 10) etc etc

Why does MSQuery change the SQL string and how can I stop it?
John

 

[SkipVought]

hi,

It's what MS query does when you have an OR along with a bunch of ANDs.

If you look at your QBE grid and the CRITERIA section, you'll see all 3 of your OR values stacked up under the WT_NOMCC and THEN in the other AND criteria, the SAM VALUES are stacked to correspond to the INDIVIDUAL values in your OR.

It bugs me too, which is why I run most of my SQL in VBA. But it should work. It's just a BEAR to maintain IN the QBE grid.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}