I have a query that is almost but not quite there ... I am querying Sage 50 Accounts using MS Query but it keeps tripping up when applying two text conditions denoted by the "IN" condition, alongside other WHERE conditions.
The problem bit is the line referring to "SAlES" or "KITS" as below :
SELECT STOCK_TRAN.STOCK_CODE, STOCK.DESCRIPTION, Sum(STOCK_TRAN.QUANTITY) AS 'Sum of QUANTITY', STOCK.SALES_PRICE, STOCK.PURCHASE_REF
FROM STOCK STOCK, STOCK_TRAN STOCK_TRAN
WHERE STOCK_TRAN.STOCK_CODE = STOCK.STOCK_CODE AND ((STOCK_TRAN.DATE Between ? And ?) AND (STOCK_TRAN.REFERENCE IN ("SALES",”KITS”) AND (STOCK.STOCK_CAT=?) AND (STOCK_TRAN.TYPE="AO") OR (STOCK_TRAN.TYPE="GO"))
GROUP BY STOCK_TRAN.STOCK_CODE, STOCK.DESCRIPTION, STOCK.SALES_PRICE, STOCK.PURCHASE_REF
Using the "IN" condition does bring back the correct results for that part of the query but report then omits the data on the other side of the "OR" statement (i.e. data that has STOCK_TRAN.TYPE="GO".
I appreciate there may be limitations of MS Query SQL but if anyone can help me with a workaround, I'd be very grateful.
PS - I m not particularly knowledgeable about SQL - just picked up bits and bobs generated by query wizards in various programs. Forgive me if it's blatantly obvious !
Dave
I've tried various versions of the SQL formula and tried the MS query Wizard in various ways but it always fails when applying the second text criteria in the WHERE condition.