Problem:

On Tue, 03 Feb 1998 16:40:22 GMT, John.S.Reynolds@asu.edu (John
Reynolds) wrote:

Here's the situation (getting up to the math)

I have two rows of dates:

Column One        Column Two
-------------------        --------------------
1/10/93	2/10/93
1/10/93	2/11/94
3/10/95	6/12/95
4/12/97	5/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 -------------------
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

'--------------- end code --------------