Well that gives me the sum, no problem, but my problem is that I'm not stating my problem very well;)

Here it goes one more time:) I guess what I'm really looking for is to pull all parts from the inventory master that match whole/part of a part number the user enters...then pull the amount on hand from the Inventory Onhand table that match the location(also specified by user), if nothing matches then onhand=0.

Perhaps I shouldn't be using a SELECT-SQL statement...the reason why I tried in the first place was that I thought that if I used a right/left join that ALL the records from the Master would be included and then only fields from records that matched from the Child would be included, all else would be .null. or something like that....oh well, I was hoping...

Now the question is: What's the best way for me to accomplish the above task?:)

>You must have another field in the InvOnHand table to specify the location. Try this:
>
>

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

The IIF() won't work to acheive what you want, but in other cases it can be used in the SELECT-SQL. The syntax should be:

SELECT IIF(EMPTY(INONHD.FONHND), 0, INONHD.FONHND) AS FONHND

BTW, is INONHD.FONHND as character field? If so, you would need to add a Val() to the Sum() in the SELECT.
>
>HTH
>
>>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?