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