I think you need a left join, instead.

Your iif expression would be better as
IIF(EMPTY(INONHD.FONHND),'0',INONHD.FONHND) AS FONHND

Good luck! (SQL has a lot of power, and you are going down the learning curve well. My hopes and wishes go with you!)

>>I'm a little bit confused here. It will help to have the structure of your tables.
>>
>>>>Try:
>>>>

SELECT Inv.Location, Count(OnHand.PK);
>>>>   FROM Inv;
>>>>   LEFT OUTER JOIN OnHand ON Inv.PK = OnHand.FK;
>>>>   GROUP BY Inv.Location

HTH
>>>
>>>But now, if I search on a part in location 01 and it's there's no stock in location 01, in which case there wouldn't be an entry in the onhand table, how do I get it to select that part number and instead say that onhand=0?
>
>Thanks for your replies,
>
>The structure: InvMast is the Master table with the description, part number, stock re-order level, etc. InvOnHand is a table with only the part number, and the quantity on hand.
>In the InvOnHand table it creates a record for each part number in each location(ie.--if part 011 has 2500 in location 01 and 32 in location 02 then there will be 2 records, if however there is 3200 in location 02 and 0 in location 01 there will be only 1 record for the location that has something on hand).
>
>Thus, I can assume that when there is no record in the onhand table, that part number has 0 on hand in that location. My question is: How do I test for this within the SQL statement?
>
>Here again is the statement I have thus far:
>

SELECT INMAST.FPARTNO, INONHD.FONHND ;
>	FROM INONHD RIGHT JOIN INMAST;
>	ON INONHD.FPARTNO=INMAST.FPARTNO;
>	WHERE &LCWHERE;
>	INTO CURSOR TMP1

>LCWHERE is concatenated according to other conditions within the form...is there a way to put like an IIF() like this?
>

SELECT INMAST.FPARTNO,IIF(EMPTY(INONHD.FONHND),'0' AS FONHND,INONHD.FONHND)...

>I've tried the above and it keeps giving me an error when the SQL is executed that it needs a )....got any ideas?