hi,
thank you,
also does TRN_QNTY have null values ? 2 records
>>

>>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 ?