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

Why don't you just put in a "where  Myds1.Fields(4) > 100" and count the
resulting records?

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, January 02, 2008 1:57 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] I need a better way to do this - looping

Here is the solution I can up with. Not elegant but works. If someone has a
better one enhance my skills.

    Do Until Myds1.EOF
        Select Case Myds1.Fields(4)
            Case Is > 100
                Myds1.MoveNext
            Case Else
                Exit Do
        End Select
    Loop
    If Myds1.EOF Then
        Myds1.Move -1
        EarliestDate = Myds1.Fields(1)
      Else
        EarliestDate = Myds1.Fields(1)
    End If
        
    DaysAbove100 = Myds.Fields(1) - Myds1.Fields(1)

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Wednesday, January 02, 2008 12:49 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com