shank wrote:
How do you return the highest value in a recordset of maybe 100
records? Is it necessary to run 2 recordsets?
I was hoping it was as simple as Max([Price]), but no luck.
Define "highest value" in something that is usually multi-dimensional, with
multiple data formats.
There are probably several solutions, and you don't really provide enough
detail for me to recommend one over another. Here's one:
While NOT RS.EOF
If RS.Fields("Pric e").Value > MaxValue Then
MaxValue = RS.Fields("Pric e").Value
End If
...other processing...
Call RS.MoveNext()
Wend
Even better, if you have used GetRows, you don't have to worry about cursor
types, and you can do all kinds of things to the data:
Rows = RS.GetRows(), priceColumn = [ integer: depends on your query ]
For i = 0 To UBound(Rows,2)
If Rows(priceColum n,i) > MaxValue Then
MaxValue = Rows(priceColum n,i)
End If
Next
Note that this can be done at any time, since it's in an array. No need to
worry about cursor types, and no need to leave connections or recordsets
open. On the other hand...
If your query is ordered on Price, just grab the first (DESC ordered query)
or last (ASC ordered one) value in the recordset.
If you don't mind a second recordset, you can use this approach:
SELECT TOP 1 Price
FROM MyTable
WHERE [ same conditions as earlier query ]
ORDER BY Price DESC
As I said, options abound.
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.