Nadya,

I always include the IN Y clause. I don't specify the alias (Y.) on each field. I only specify the alias on fields outside of the target like X in this case.

replace TotRooms with 10000, BedRooms with 1000, ;
PRSLDATE WITH GetPrSlInfo(X.PROPID, X.SOURCE, X.DATE,'Date') IN Y

Now as to the function...

If the following seek lands your record pointer on the last record in TranMstr, the skip will leave you on EOF() in TranMstr

if m.tcSource = "S" and seek(str(m.tnPropId,9)+dtos(m.tdDate),"TranMstr","TrxnHist")
select TranMstr
set order to TrxnHist
if !eof()
skip


I gave up on your code after that point.

I did this little program and it ran correctly for 118,000 records before I stopped it. I even stopped it and reran it, so that mylog already existed and wasn't recreated. It appears you can CREATE CURSOR in a UDF called during a replace command.

USE speed
replace ALL spd_char WITH myudf(spd_id)
RETURN

FUNCTION myudf
LPARAMETERS tiID
LOCAL lnSelect
lnSelect = SELECT()
SELECT 0
IF !used("mylog")
  CREATE CURSOR mylog (log_id i)
ENDIF
APPEND BLANK IN mylog
replace log_id WITH m.tiID IN mylog
SELECT (lnSelect)
RETURN STR(m.tiID)

You really should trace the code and see why its failing. Don't guess what's wrong, prove, know! understand! I know I sound like Yoda in Star Wars, but don't start trying a variety of solutions without knowing why you have the problem!

Now, you still have to go and figure out what's wrong with the code.

>Hi everybody,
>
>Here is a problem, which we're experiencing:
>
>we have input table aliased as X and output table aliased as Y.
>
>Here is replace command, we're trying, sitting on X:
>replace y.TotRooms with 10000, y.BedRooms with 1000, ;
>y.PRSLDATE WITH GetPrSlInfo(X.PROPID, X.SOURCE, X.DATE,'Date')
>
>where TotRooms is N (4.1) and Bedrooms N(2).
>
>Here is the code for GetPrSlInfo UDF:
>
>

>********************************************************************************
>function GetPrSlInfo
>*  Description.......: Function returns the previous sale info if it exists
>*  Calling Samples...: GetPrSlInfo(PROPID,SOURCE,DATE,"Date","C",8)
>*  Parameter List....: tnPropid, tcSource, tdDate, tcFieldName, tcRetType (Optional), tnNumChar (Optional)
>*  Created by........: Nadya Nosonovsky 08/15/01 12:04:59 PM
>*  Modified by.......: Nadya Nosonovsky 08/15/01 12:05:02 PM
>********************************************************************
>lparameter tnPropId, tcSource, tdDate, tcFieldName, tcRetType, tnNumChar, lnRecno, lnReccount
>local lnSelect, lVar
>lnSelect=select()
>lnRecno = recno() && Assuming we're sitting on the correct file
>lnReccount = reccount()
>if !used('curPrevSlInfo')
>	select 0
>	create cursor curPrevSlInfo (RecNum I, date D, Price I, Book I, page I)
>	append blank	
>endif
>if curPrevSlInfo.RecNum <> m.lnRecno
>	replace RecNum with m.lnRecno in curPrevSlInfo
>*   lVar=CreateEmptyVar(m.tcFieldName,'TranMstr')
>	if m.tcSource = "S" and seek(str(m.tnPropId,9)+dtos(m.tdDate),"TranMstr","TrxnHist")
>		select TranMstr
>		set order to TrxnHist
>		if !eof()
>			skip
>			if TranMstr.PropID = m.tnPropId
>				do while TranMstr.PropID = m.tnPropId
>					if TranMstr.source = "S" and TranMstr.date < m.tdDate					
>					        replace Date with TranMstr.Date, ;
>					                Price with TranMstr.Price, ;
>					                Book with TranMstr.Book, ;
>					                Page with TranMstr.Page in curPrevSlInfo
>*!*							scatter memvar
>*!*							insert into curPrevSlInfo from memvar
>						exit
>					endif
>					if !eof()
>						skip
>					else
>						exit
>					endif
>				enddo
>			endif
>		endif
>	endif
>endif
>lVar = evaluate('curPrevSlInfo.'+ m.tcFieldName)
>if m.lnRecno=m.lnReccount && Last record
>   use in curPrevSlInfo
>endif
>select (m.lnSelect)
>if !empty(m.tcRetType)
>	if vartype(m.lVar)<>m.tcRetType
>		return ConvertType(m.lVar,m.tcRetType,m.tnNumChar)
>	endif
>else
>	return m.lVar
>endif

>
>I ran couple of tests in command window. If I ran it first time, when curPrevSlInfo is not created, I didn't get a Numeric Overflow error. The second time I run my test5 program, I get this error... I see some other weirdness as well. When I run this transformation for multiple records, it doesn't produce errors and the job runs till completion...
>
>As a workaround I can change this program to either create public variables (and release them at last record) or create public array, or add array to the _screen (or _vfp) object instead of using cursor. I guess, we probably can not create a cursor during replace command...
>
>Anyway, it's very weird... Windows 95 VFP6 SP4.
>
>Can you please replicate my findings and suggest a workaround?
>
>Thanks a lot in advance.