hi,
thank you,
1.i get error message Field "Qty_In" does not accept null values
at line.. replace Qty_In with C_Subtotal.Qty_In,;
2. i need to insert between 2 date as ..
BETWEEN (TRN_DATE,thisform.text2.Value,thisform.text3.Value);

from ;
          trn_rqb3;
       where ;
              trn_rqb3.str_nmbr = C_QualifiedStockItems.str_nmbr;
          AND trn_rqb3.stk_code = C_QualifiedStockItems.stk_Code;
         AND BETWEEN (TRN_DATE,thisform.text2.Value,thisform.text3.Value);
 

thanks

>It looks like you can do this in one SQL statement, but will keep your flow.
>
>

>lnStockNumber = Thisform.text1.value
>lnStockClassFrom = Thisform.text4.value
>lnStockClassTo = Thisform.Text5.value
>
>*/ First, get qualified stock items and their respective opening balances
>*/ I am adding a few "place-holder" columns that will be updated during
>*/ the scan loop.  Not all 3 are required, but will allow for you to see
>*/ what I am getting values from.
>SELECT ;
>      str_nmbr,;
>      stk_name,;
>      stk_clas,;
>      stk_code,;
>      stk_opbl,;
>      000000000 as Qty_In,;
>      000000000 as Qty_Out,;
>      000000000 as NewBalance;
>   FROM ;
>      stk_clas ;
>   WHERE ;
>          str_nmbr = lnStockNumber;
>      AND between( stk_clas, lnStockClassFrom, lnStockClassTo );
>   INTO ;
>      CURSOR C_QualifiedStockItems READWRITE
>
>*/ Now, use THIS cursor to loop and get sum of inventory in/out
>select C_QualifiedStockItems
>scan
>    use in select( "C_Subtotal" )
>    select ;
>          sum( Trn_Qnty * iif( trn_typ = 1, 1, 0 )) as Qty_In,;
>          sum( Trn_Qnty * iif( trn_typ= 44 or trn_typ=55, 1, 0 )) as Qty_Out;
>       from ;
>          trn_rqb3;
>       where ;
>              trn_rqb3.str_nmbr = C_QualifiedStockItems.str_nmbr;
>          AND trn_rqb3.stk_code = C_QualifiedStockItems.stk_Code;
>       into ;
>          cursor C_Subtotal
>
>    select C_QualifiedStockItems
>    replace Qty_In with C_Subtotal.Qty_In,;
>            Qty_Out with C_Subtotal.Qty_Out,;
>            NewBalance with Stk_Opbl + C_Subtotal.Qty_In - C_Subtotal.Qty_Out
>
>endscan
>
>*/ You should be good to go from here... with whatever adjustments you need.
>
>
>