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