Trouble Concatinating String in loop

[gammaman]

I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want

Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
columnCount = columnCount - 1
If I = 0 Then
strSQL = rs.Fields(I) & "=" & rs2.Fields(I) & "=" & rs2.Fields(I)
Else
strSQL = strSQL & "AND" & rs.Fields(I) & "=" & rs2.Fields(I)
'MsgBox I
End If
Next I
MsgBox strSQL & strSQL2

I am trying to achieve something that reads like the following

r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND

.....

 

[vbaInet]

Something like this? Untested but should work:

[COLOR="Red"]    Dim db As DAO.Database[/COLOR]
    Dim fld1 As DAO.Field
    Dim flds2 As DAO.Field2
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("r1")
    Set flds2 = db.OpenRecordset("r1_old").Fields
    
    For Each fld1 In rs.Fields
        strSQL = fld1 & " = " & flds2(i) & " AND "
        i = i + 1
    Next
    
    strSQL = Left(strSQL, Len(strSQL) - 5)
    
    MsgBox strSQL & strSQL2

The highlighted line is something that you must remember to do everything you want to open a recordset. You must explicitly refer to the database instance, in this case your current db.

 

[MarkK]

So you end up with SQL like . . .

6 = 5 AND 12 = 10 AND This is a text field = foo

. . . but I don't see how that's all that useful. What is the bigger picture problem you want to solve? Do you want to find duplicates?

If so, create one table, maybe r1_all. Add a field called IsOld. Insert all records from r1 setting IsOld = false. Insert all records from r1_old, setting IsOld = true. Having that data in different tables is a huge headache.

Now you can GROUP BY different fields and use SQL to count records that have fields in common. Consider this SQL . . .

SELECT Field1, Field2, Count(*) As Duplicates, Sum(Abs(IsOld)) As OldCount
FROM r1_all
GROUP BY Field1, Field2;

. . . see what's happening there? Say there are 5 records that have Field1 and Field2 in common. The Count(*) As Duplicates field will return 5, and then, since True = -1, Sum(IsOld) As OldCount counts the old ones, and we use the absolute value Abs() function to return a positive number.

So it counts all matching records and counts all the matching old ones. No Loops. No recordsets. Hope that helps,