[AccessD] I need a better way to do this - looping

Chester,

I would use two queries and code to capture your data.

1. Query 1 captures the last date where the measurement is < 101. I called this query qryLastMeasurement.

SELECT Last(tblTest.fldDate) AS LastDateUnder101
FROM tblTest
WHERE (((tblTest.fldMeasurement)<101));

2. Query 2 would use query 1 and capture the number of records that are greater than 100 with dates greater than the one captured in the first query. This query's name is qryTotalMeasurements.

SELECT Count(tblTest.fldMeasurement) AS TotalOver100 FROM tblTest, qryLastMeasurement
WHERE (((tblTest.fldDate)>[Lastofflddate]) AND ((tblTest.fldMeasurement)>100));

3. Here's the procedure to capture the total.

Public Sub CountMeasurements()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim intTotal As Integer
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryTotalMeasurements")
    
    intTotal = 0
        
    Do While Not rst.EOF
        intTotal = rst.Fields(0)
        rst.MoveNext
    Loop
    
    EarliestDate = intTotal
    
    rst.Close
    dbs.Close
    Set rst = Nothing
    Set dbs = Nothing
    
    
End Sub

Will this work for you?

papparuff

--
John V. Ruff – The Eternal Optimist :-) 

“Commit to the Lord whatever you do, 
and your plans will succeed.” Proverbs 16:3

-------------- Original message -------------- 
From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com> 

> I use the following code to loop through a record set until the field 
> being checked is less than 100. This works great unless I run out of 
> records before the condition is met. When that happens 
> 
> Earliest date is equated to nothing (no current record). What might be a 
> better way to do this? Thanks. 
> 
> 
> 
> Do Until Myds1.EOF 
> 
> Select Case Myds1.Fields(4) 
> 
> Case Is > 100 
> 
> Myds1.MoveNext 
> 
> Case Else 
> 
> Exit Do 
> 
> End Select 
> 
> Loop 
> 
> EarliestDate = Myds1.Fields(1) 
> 
> 
> 
> Chester Kaup 
> 
> Engineering Technician 
> 
> Kinder Morgan CO2 Company, LLP 
> 
> Office (432) 688-3797 
> 
> FAX (432) 688-3799 
> 
> 
> 
> 
> 
> No trees were killed in the sending of this message. However a large 
> number of electrons were terribly inconvenienced. 
> 
> 
> 
> -- 
> AccessD mailing list 
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd 
> Website: http://www.databaseadvisors.com