General information
Category:
Coding, syntax & commands
>>Hi all,
>>
>>I'm trying to search a table for 'missing' records. I want to scan through my 'agency' table for agency i.d. codes, then search another table (newinit in this case) to see if there is a record for agency code (field=acode) = the value found in the scan of the agency table.
>>
>>I'm using the following code to do this (VFP5.0), but I'm coming up with an endless loop in the DO WHILE section in which I'm getting 'millions' of records with acode=myscanvalue, but a new ntid (my key field formy newinit table) and I have to cancel the program.
>>
>>Any clue what I'm doing wrong here?
>>
>>*****
>>USE newinit
>>USE agency in 0
>>SELECT agency
>>SET ORDER TO acode
>>GO TOP
>>SCAN
>> myscanvalue=alltrim(acode)
>> SELECT newinit
>> LOCATE FOR alltrim(acode) = myscanvalue
>> DO WHILE !FOUND( )
>> SET ORDER TO ntid
>> GO BOTTOM
>> x=ntid
>> x='00'+alltrim(str(val(x)+1))
>> APPEND BLANK
>> REPLACE acode with myscanvalue
>> REPLACE ntid with x
>> ENDDO
>> SELECT agency
>>ENDSCAN
>>********
>>
>>MTIA!!
>>
>>Mandy
>
>Let me show you another aproach rather than look for the bug here.
>try this on your own risk:
>
>USE newinit
>SET ORDER TO NTID
>GO BOTTOM
>LASTNTID = NTID
>SET ORDER TO ACODE
>*Well, index on acode if not yet done
>USE agency in 0
>SELECT agency
>SET ORDER TO acode
>SET RELATION TO ACODE INTO NEWINIT
>GO TOP
>SCAN FOR EOF("NEWINIT")
> x=VAL(LASTNTID)
> x='00'+alltrim(str(val(x)+1))
> APPEND BLANK
> REPLACE acode with AGENCY.ACODE
> REPLACE ntid with x
> LASTNTID = VAL(LASTNTID) + 1
> SELECT AGENCY
>ENDSCAN
>
>The trick here is that once the relation is made, the child table goes to eof() when the parent table is on an unmatched record.
>
>Hope this work.
Thanks much for your suggestion. I did just find a way to get my idea to work. I changed the "DO WHILE !FOUND( )" line to "DO WHILE EOF()" and it worked just fine. I guess VFP wasn't too fond of my attempt to use !FOUND.
Thanks again,
Mandy
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only