module to compare line by line record

[EBee]

Private Sub Buildfile()

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim TimeValue As Integer


Set db = CurrentDb
Set rs = db.OpenRecordset("auxil1", dbOpenDynaset)
Set rs2 = db.OpenRecordset("auxil_logic_open", dbOpenDynaset)

Do While Not rs.EOF
'Debug.Print rs![Number], rs2![Number]
If (rs!Number) = (rs2!Number) Then
If (rs!Trans_Number) < 7 and Then
If (rs!tx_Date) = (rs2!tx_Date) Then

Debug.Print rs!Number
End If
Else
rs.Delete
End If

rs.MoveNext
Loop
rs.Close
rs2.Close

I am trying to compare two tables and pick some information in it depending on the condition. I am having a tough time figuring this out. I need module to compare line by line and pick the line record and write it to another table.

thank

erwin

 

[MichaelRed]

Your inquiry is not sufficiently complete for me to offer mush advice. The posted code will search rs for all occurances of a 'match' to the criteria in the first (AND ONLY TE FIRST) record in rs2, which appears to be a small (or nonexistant) intersect.

Further, you appear to be using a limited criteria set -which suggests that a simple query would be (more) appropiate to your problem. I 'guess' you are looking for all of matches of Number and tx_date between the two recordsets where rs!Trans_Number < 7, so a simple query using the first two fields as equ-joins and the third as a criteria would return the recordset of interest, which you could process rs!number in what ever manner you need.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[EBee]

Private Sub Buildfile()

Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim dbs As Database, tdf As TableDef, fld As Field

Set db = CurrentDb
Set rst = db.OpenRecordset(&quot;auxil1&quot
Set rstOpen = db.OpenRecordset(&quot;Auxil_Logic_Open&quot

Do While Not rst.EOF

If rst!Number = rstOpen!Number And rst!Trans_Number = 6 And rst!Tx_Date = rstOpen!Tx_Date Then
If (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60) Then

Debug.Print rst!Number
Else
rst.Delete
End If
rst.MoveNext
End If
Loop


End Sub

module. . help ?? I am trying to do, and if there is a way to use a simple query that would be better.

Auxil table 1
Number Tx_Date Tx_Time Trans_Number
-------------------------------------
11154 8/1/01 7:26:38 AM 6
31062 8/2/01 10:31:29 AM 7


Auxil_Logic_Open table 2( all trans_Number = 18's)
Number Tx_Date Tx_Time Trans_Number
-------------------------------------
11154 8/1/01 7:25:38 AM 18
31062 8/2/01 10:30:29 AM 18

i am trying to match all occation of 18's with the same Number and trans_Number 6 in table 1, andb if TX_Time between the two number is less than 60 sec. Copy line in table 1 and line in table 2 and create a new table

should look like
NewTable
18
6
18
6

then table 2 should only be left with a few 18 because it did not find a match.

thanks
erwin