hi,
thank you very much,
have a nice time

>Add...
>
> if reccount( "C_Subtotal" ) = 1
> 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
>
> endif
>
>
>>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.
>>>
>>>
>>>