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

You could try this (aircode):-

Dim dbs as dao.database, rst as dao.recorset, sql as string
Dim varDateResponse as variant
Set dbs = currentdb
Sql = "Select PressureDate,PressureReading from tblReadings where
PressureReading < 100 Order By PressureDate DESC"
Set rst = dbs.openrecordset(sql)
If rst.eof then
	varDateResponse = dcount("*","tblReadings")
Else
	varDateResponse = rst!Pressuredate ' take first record
End if

Max



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, January 02, 2008 6:35 PM
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