CompareDB

Private Sub Form_Load()
Dim db As Database
Dim tb As TableDef
Dim fld As Field
Dim indx As Index

Dim rs As Recordset
Dim ds As Recordset
Dim strSQL As String
Dim intX As Integer
Dim StartDate As Date

Const DBName As String = "\john.mdb"

If Dir(App.Path & DBName) <> "" Then Kill App.Path & DBName
'make database

Set db = CreateDatabase(App.Path & DBName, dbLangGeneral)
'make a table

Set tb = db.CreateTableDef("dates")
'make two date-fields

Set fld = tb.CreateField("dateone", dbDate)
tb.Fields.Append fld
Set fld = tb.CreateField("datetwo", dbDate)
tb.Fields.Append fld
db.TableDefs.Append tb
Set db = OpenDatabase(App.Path & DBName)
Set rs = db.OpenRecordset("dates")

'fill field 1

StartDate = Format(Now, "Short Date")
For intX = 1 To 5000
Randomize
StartDate = DateAdd("m", CInt((12 * Rnd) + 1), StartDate)
StartDate = DateAdd("d", CInt((30 * Rnd) + 1), StartDate)
With rs
.AddNew
.Fields(0).Value = StartDate
.Update
End With
Next intX

'fill field 2

rs.MoveFirst
StartDate = Format("01-01-1998", "Short Date")
Do While Not rs.EOF
Randomize
StartDate = DateAdd("m", CInt((12 * Rnd) + 1), StartDate)
StartDate = DateAdd("d", CInt((30 * Rnd) + 1), StartDate)
With rs
.Edit
.Fields(1).Value = StartDate
.Update
End With
rs.MoveNext
Loop
rs.MoveFirst
Do While Not rs.EOF
intX = 0
strSQL = "SELECT dates.dateone, dates.datetwo From dates _
WHERE (((dates.datetwo)>#" & rs.Fields(0).Value & "#));"
Set ds = db.OpenRecordset(strSQL)
If Not (ds.BOF And ds.EOF) Then
Do While Not ds.EOF
intX = intX + 1
ds.MoveNext
Loop
MsgBox " found " & CStr(intX) & " times greater"
End If
ds.Close
rs.MoveNext
Loop
rs.Close
db.Close

End Sub

Column One Column Two
------------------- --------------------
1/10/932/10/93
1/10/932/11/94
3/10/956/12/95
4/12/975/12/97
where the second column will always be > the first column.
These are actually database records, numbering in the thousands.
For every record that is read in column one, I need to check all
*prior* dates in column two and see if they are greater than the date
I have in column one. I am putting the sum total of the result in
Answer:
Try as an example the next code; just make a new project; a form and
insert the code into the form_load event. The example make an access
database with just two fields (dateone and datetwo) and 5000 records.
The date are randomized (as far as VB let you do this;-).
After making teh database and fill the table it just starts with the
first record; uses the value in a SQL statement to get all the records
where teh value in the field 'datetwo' is greater then the value in
field 'dateone'. A simple counter let you see how many records there
are. But you can easily change it and - for example - show the found
date instead of just counting.
------------------ code -------------------