finding records

[ozdemirozgur]

Hi,

I have two tables with 1-n relation. Each table has a field called Coy_Id which is a company code. I need to find the records from the second table with the same Coy_Id and another field called HLDR_DESC which is not empty. then I need to update the main table if there is a record on the second table with unempty HLDR_DESC. I used the following code but it seems it is too long and not working anyway. please can you help?

/CODE/
Do While Not Main10_2000_Combined.EOF
Do While Not EXTRHOLD10.EOF
If (EXTRHOLD10!Coy_Id = Main10_2000_Combined!Coy_Id) Then
If IsNull(EXTRHOLD10!HLDR_DESC) = False Then
Main10_2000_Combined.Edit
Main10_2000_Combined!Dup2010 = "Y"
Main10_2000_Combined.Update
End If
End If
EXTRHOLD10.MoveNext
Loop

EXTRHOLD10.MoveFirst

Main10_2000_Combined.MoveNext
Loop
Main10_2000_Combined.MoveFirst
End Sub

/CODE/

 

[apr pillai]

Modify the line: If IsNull(EXTRHOLD10!HLDR_DESC) = False Then to

If len(trim(Nz(EXTRHOLD10!HLDR_DESC,""))) > 0 Then

and try.

 

[ozdemirozgur]

Hi,

I htink, it works but my transaction table has a couple of million recordss, it is taking too long. any idea how I can change the entire logic of the code?

 

[spikepl]

Forget code and make it all in a query. That will be much faster.