Iterate through records in a query result and perform operations?

[Mark Dyson]

I'm trying to figure out how to iterate through the results of a query,
perform some logical evaluations at certain points in the process, and return
the records that evaluate true, until I've exhausted the records in the query.

The following is a snippet of data (first row holds names of the fields)
from a sample query. The 'seq' is just an internal sequence value, the 'bfc'
and 'hwt' are attributes whose values will be prompted for. The 'oper' is a
code which in this case means 'equal to', the 'value' is the value to be
compared to the prompted-for value, and the connector links the line to the
next one; 1 means logical or, 2 is logical and, while 0 means it's the last
line of a block.

cond_index|seq|att|oper|value|connector
310|1|bfc|1|0|1
310|2|bfc|1|1|1
310|3|bfc|1|4|1
310|4|bfc|1|9|1
310|5|bfc|1|10|1
310|6|bfc|1|11|1
310|7|bfc|1|16|1
310|8|bfc|1|17|1
310|9|bfc|1|18|1
310|10|bfc|1|19|1
310|11|bfc|1|20|1
310|12|bfc|1|22|1
310|13|bfc|1|23|1
310|14|bfc|1|24|1
310|15|bfc|1|27|1
310|16|bfc|1|83|1
310|17|bfc|1|999|0
311|1|bfc|1|15|0
312|1|bfc|1|2|1
312|2|bfc|1|3|1
312|3|bfc|1|5|1
312|4|bfc|1|12|1
312|5|bfc|1|13|1
312|6|bfc|1|14|0
313|1|bfc|1|7|2
313|2|hwt|1|0|1
313|3|hwt|1|2|1
313|4|hwt|1|3|1
313|5|hwt|1|4|1
313|6|hwt|1|7|0
314|1|bfc|1|7|2
314|2|hwt|1|11|1
314|3|hwt|1|14|1
314|4|hwt|1|15|1
314|5|hwt|1|16|0

What I need to do is return the 'cond_index' values for any block of data
where the prompted-for value for an attribute returns True when evaluated
against the stored value via the logical operators. In the case of
cond_index 310, I would select it if the user entered a value for 'bfc' that
evaluated true for:

"bfc = ( 0 or 1 or 4 or 9 or 10 or 11 or 16 or 17 or 18 or 19 or 20 or 22 or
23 or 24 or 27 or 83 or 999 )"

I would return a cond_index of 314 if:

"bfc = 1 and hwt = ( 11 or 14 or 15 or 16 )"

The difficulty I'm having is figuring out what facilities Access provides
that would let me iterate through a set of records returned by a query,
reading in lines and evaluating them until I reach some trigger (such as a
connector of '0') and then repeating that process until I've exhausted the
data in the query. I'm sure this isn't rocket science, but poring over help
files and doing web searches for clues as to how to approach this aren't
bearing much fruit. Is there some way to define things using a "For
Each/Next" loop? Are there built-in iterators for going through the results
of a query and stuffing them into local variables for evaluation? Is there
some inherently straightforward way to approach this that to an
Access-experienced person seems simple?

Advice and/or pointers to the source of same will be greatly appreciated.
Thanks in advance!

Mark

 

[Mark Dyson]

The web interface told me there was an error posting and that I was to try
again, so I did. Go figure.

 

[John Nurick]

Hi Mark,

Any time you need to iterate through the results of a query, use a
recordset, something like this:

Dim rsR As DAO.Recordset

Set rsR = CurrentDB.OpenRecordset "MyQuery"

With rsR
Do Until .EOF()
'Do stuff
...
.MoveNext
Loop
.Close
End With


However, surprisingly often it's possible to use set operations (i.e. a
query) to do things that at first sight require a sequential approach.
It may be worth posting a revised question, with a clearer explanation
of the results you want from given user input, in
microsoft.public.access.queries, where the SQL wizards hang out.



On Fri, 15 Oct 2004 10:35:26 -0700, "Mark Dyson" <Mark