>>>But they are not. It seems I had it backwards. It works as long as there is an ORDER BY. With no ORDER BY, the numbers are wild.
>>>
>>>I ran the query with no grouping to see if I could tell what was happening. What is happening is that without the ORDER BY, this phrase:
>>>

>>>
>>>				WHERE b.control_no in ;
>>>					(SELECT towfky FROM redeem WHERE redeem.dredeemdat>= {01/01/2014} AND redeem.dredeemdat<= {12/31/2014}) ;
>>>
>>>

>>>
>>>is responding true and including records where redeem.dredeemdat is empty!
>>
>>Of course it will include these records.
>>You check IF b.control_no in some values between some dates, BUT what happens
>>if some b.control_no IS in that list but have empty date?
>>It will be included in the result.
>
>Actually, I just realized. We should have checked the towKy - what if this key is NULL? Then it will explain the problem.
Naomi,
run

CREATE CURSOR ca (i1 c(1) NULL, d1 DATE)
INSERT INTO ca VALUES ("1", DATE())
INSERT INTO ca VALUES ("2", {})

CREATE CURSOR cb (i2 c(1) NULL,i3 c(1), i4 D)
INSERT INTO cb VALUES ("1","A", DATE())
INSERT INTO cb VALUES ("1","A", {})
INSERT INTO cb VALUES ("2","B", {})
INSERT INTO cb VALUES ("","C", {})

*no record C BUT record A with empty date
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

*no record C BUT record A with empty date
INSERT INTO ca VALUES ("", {..})
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

*record C AND record A with empty date
INSERT INTO ca VALUES ("", DATE())
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

*record ca with null
INSERT INTO ca VALUES (.NULL., DATE())
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

*record ca with null, wrong date
INSERT INTO ca VALUES (.NULL., {})
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

*record ca with null, right date
INSERT INTO ca VALUES (.NULL., DATE())
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

*record cb with null
INSERT INTO cb VALUES (.null.,"N", DATE()-3)
SELECT * FROM cb WHERE i2 IN (SELECT i1 FROM ca WHERE d1<=DATE()+1 AND d1>=DATE()-1)

Null will do nothing

because only .T. OR .NULL. returns .T.

My guess is that you should not do the subselect in WHERE. Because WHERE runs against result set.
Try as JOIN. This will run before the result set is created.

Update
Possibly as SELECT field,sum(Field) FROM (SELECT ...) INTO .. GROUP BY .. ORDER BY ...

this will eleminate the problems with the group order problem in the inner SELCT and the outer does the sum / grouping