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

I agree with some of the other posts...this can be done in a single query.

for this example a single table...with 2 fields DT(date) and MS(Measure)...Use a subquery to find the max date where the MS was under 100...use that as a filter in your query and ask for everything greater than that date.

**********START SQL*************
SELECT Count(*) AS Records
FROM tblTest
WHERE (((tblTest.dt)>(SELECT Max(tblTest.dt) AS MaxOfdt
FROM tblTest
WHERE (((tblTest.MS) Date: Wed, 2 Jan 2008 10:51:57 -0600
> From: Chester_Kaup at kindermorgan.com
> To: accessd at databaseadvisors.com
> 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
>
>
>
>
>
> 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

_________________________________________________________________
Share life as it happens with the new Windows Live.
http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_122007