Developer Forum

ello

I appologize ahead of time...

this is going to be nasty...

SELECT
dbo_CUSTOMER_ORDER.CUSTOMER_ID AS CustID,
dbo_CUSTOMER.NAME, dbo_CUSTOMER_ORDER.ID,
dbo_CUSTOMER.COUNTRY,
dbo_CUSTOMER_ORDER.CUSTOMER_PO_REF AS CustPO,
dbo_CUSTOMER_ORDER.ORDER_DATE AS CODate,
dbo_CUSTOMER_ORDER.SELL_RATE,
dbo_CUSTOMER_ORDER.CURRENCY_ID,
IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10]))))) AS Priority,
DateDiff("d",[ORDER_DATE],Now()) AS Age,
dbo_CUSTOMER_ORDER.DESIRED_SHIP_DATE AS DShipDate,
DateDiff("d",Now(),[dbo_CUSTOMER_ORDER].[DESIRED_SHIP_DATE]) AS DaysDue,
dbo_CUSTOMER_ORDER.PROMISE_DATE AS PromDate,
DateDiff("d",Now(),[dbo_CUSTOMER_ORDER].[PROMISE_DATE]) AS PDaysDue,
dbo_CUSTOMER_ORDER.STATUS AS COStatus,

Sum(IIf([TRADE_DISC_PERCENT]>=1,0,IIf([ORDER_QTY]-[TOTAL_SHIPPED_QTY]>0,(([ORDER_QTY]-[TOTAL_SHIPPED_QTY])*IIf([dbo_CUSTOMER_ORDER]![CURRENCY_ID]<>"CDN",[UNIT_PRICE]*[SELL_RATE],[UNIT_PRICE])),0)))
AS OpenValue,

Sum(IIf([TRADE_DISC_PERCENT]>=1,0,IIf([ORDER_QTY]-[TOTAL_SHIPPED_QTY]<=0,([ORDER_QTY]*IIf([dbo_CUSTOMER_ORDER]![CURRENCY_ID]<>"CDN",[UNIT_PRICE]*[SELL_RATE],[UNIT_PRICE])),0))) AS ClosedValue,

Sum(IIf([TRADE_DISC_PERCENT]>=1,0,[ORDER_QTY]*IIf([dbo_CUSTOMER_ORDER]![CURRENCY_ID]<>"CDN",[UNIT_PRICE]*[SELL_RATE],[UNIT_PRICE]))) AS TotalOrderValue,
dbo_CUST_ORDER_LINE.GL_REVENUE_ACCT_ID AS GLID,
dbo_CUST_ORDER_LINE.PRODUCT_CODE AS Pcode,
dbo_CUST_ORDER_LINE.TRADE_DISC_PERCENT AS Disc,
IIf([dbo_CUST_ORDER_BINARY].[BITS] Is
Null,"",AlanByteArrayToString([dbo_CUST_ORDER_BINARY]![BITS])) AS OrderSpecs,
IIf([AftermarketSales]=-1,"True","False") AS VSPF,
IIf([VisualOrderName] Is
Null,[dbo_CUSTOMER_ORDER].[SALESREP_ID],[VisualOrderName]) AS VOSP,
IIf([dbo_CUSTOMER_ORDER].[USER_10] Is Null,"No Priority",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency","Emergency",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Service","Service",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Emergency MD","Machine Down",
IIf([dbo_CUSTOMER_ORDER].[USER_10]="Stock","Stock",[dbo_CUSTOMER_ORDER].[USER_10]))))) AS PriFilter
FROM
qryASPOrderTracker
RIGHT JOIN (((dbo_CUSTOMER
RIGHT JOIN dbo_CUSTOMER_ORDER
ON dbo_CUSTOMER.ID = dbo_CUSTOMER_ORDER.CUSTOMER_ID)
LEFT JOIN dbo_CUST_ORDER_LINE
ON dbo_CUSTOMER_ORDER.ID = dbo_CUST_ORDER_LINE.CUST_ORDER_ID)
LEFT JOIN dbo_CUST_ORDER_BINARY
ON dbo_CUSTOMER_ORDER.ID = dbo_CUST_ORDER_BINARY.CUST_ORDER_ID)
ON qryASPOrderTracker.VisualOrderName = dbo_CUSTOMER_ORDER.SALESREP_ID
WHERE
(((IIf([AftermarketSales]=-1,"True","False")) In
((IIf([Forms]![frmOrderTracker]![tglAftermarket] Is Null,"False",
(IIf([Forms]![frmOrderTracker]![tglAftermarket]=-1,"True","False")))),
(IIf([Forms]![frmOrderTracker]![tglAftermarket] Is Null,"True",
(IIf([Forms]![frmOrderTracker]![tglAftermarket]=0,"False","True"))))))
AND
((Year([ORDER_DATE]))>=IIf([Forms]![frmOrderTracker]![cboFromYear]="All",1900,[Forms]![frmOrderTracker]![cboFromYear]))
AND ((dbo_CUSTOMER_ORDER.STATUS)
In(IIf([Forms]![frmOrderTracker]![tglFirmed]=False,"f",""),
IIf([Forms]![frm

Assuming you actually have, symbolically:

...
WHERE x
GROUP BY y
HAVING z


then, modify it to have:

...
WHERE (x) AND (z)
GROUP BY y



ie, add a pair of parenthesis around the content that you have in the WHERE
and in the HAVING clause, that does not hurt the performance, add an AND to
the modified where clause, and bring the content of the now modified having
clause and remove the key word HAVING now left without any content.



Vanderghast, Access MVP