Hello,

I'm currently rewriting Access queries for SQL 7.0, my problem is a WHERE statement with a large number of IIf conditions. I'm not sure whether I should be trying to rework the conditions into a CASE statement or whether I should use nested IF statements before the SELECT... (or another approach entirely!)

Any help/insight offered will be gratefully recieved!!!!

Heres my code ( WHERE conditions truncated )...

*****
PARAMETERS lDealerID Long;
SELECT tblDealerIncludes.DealerID, tblStock.StockID AS StockIDInclude

FROM tblDealerMake RIGHT JOIN
(tblDealerCustom INNER JOIN
(tblDealerIncludes INNER JOIN tblStock ON tblDealerIncludes.IncludeID = tblStock.DealerID)
ON tblDealerCustom.DealerID = tblDealerIncludes.DealerID)
ON tblDealerMake.DealerID = tblDealerIncludes.DealerID

WHERE (((tblStock.Fuel) Like [tblDealerCustom]![FuelType])
AND ((IIf([tblStock]![Category]='Sedan' And [tblDealerCustom]![Sedan]=False,False,True))=True)
AND ((IIf([tblStock]![Category]='Sport' And [tblDealerCustom]![Sport]=False,False,True))=True)
...
...
...
AND ((tblStock.Sold)=0)
AND ((tblStock.Hold_ToConfirm)=False)
AND ((tblDealerIncludes.DealerID)=[lDealerID]))

GROUP BY tblDealerIncludes.DealerID, tblStock.StockID, tblStock.Make

HAVING (((Sum(IIf([tblStock]![Make]=[tblDealerMake]![Make],True,False)))=0))

UNION

SELECT tblStock.DealerID, tblStock.StockID AS StockIDInclude
FROM tblStock

WHERE tblStock.DealerID = [lDealerID] and tblstock.sold=0
;
*****