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

So if you wind up with Nothing, you use the count of records, NO?

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, January 02, 2008 10:35 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] I need a better way to do this - looping

Let me try to explain what I am trying to calculate. Myds.Fields(4) is a
pressure variance measurement on an oilfield injection well. It should
always be below 100. The goal is to count the number of records (date
descending) from the most recent to the first one where Myds1.Fields(4)
is less than 100 for each well. The number of records per well varies.
If all the records for a well have Myds.fields(4) with a value above 100
the correct answer is the number of records for the well. If record 11
descending from the most recent is less than 100 then the correct answer
would be 10. Hope this makes sense. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Wednesday, January 02, 2008 11:30 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] I need a better way to do this - looping

I don't understand.  If you run out of records before the condition is
met, then isn't Nothing the right answer?

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, January 02, 2008 8:52 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] I need a better way to do this - looping

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