>

>CLOSE ALL
>USE test
>brow&&i can see my data(found)
>ldStart =ctod("01/01/2006")
>ldEnd = ctod("31/12/2006")
>
>SELECT stk_code,trn_type,trn_date,cst_prce,str_nmbr, ;
>	SUM(IIF(MONTH(trn_date)=1 AND trn_type=1,trn_qnty, 000000)) AS m1_t ;
>	FROM test ;
>WHERE between(trn_date,ldStart,ldEnd)&&no result
>WHERE between(trn_date,date(2006, 12, 31))  ;error
>GROUP BY stk_code,trn_type,trn_date,cst_prce,str_nmbr
>

trn_date = ??? is it defined ?

try shifting the iif outside the sum like

SELECT stk_code,trn_type,trn_date,cst_prce,str_nmbr, ;
SUM(trn_qnty) AS m1_t ;
FROM test ;
WHERE MONTH(trn_date)=1 AND trn_type=1 and between(trn_date,ldStart,ldEnd) ;
GROUP BY stk_code,trn_type,trn_date,cst_prce,str_nmbr

also does TRN_QNTY have null values ?