Min & Max from Recordsetclone? no underlying table or query (1 Viewer)

Hi,

Perhaps a rare (or stupid request) as cannot find anyone else asking.

Trying to find a way to get Min & Max values of a field on a continuous form that is not necessarily base on a query.

Seemed simple & quickest to me to use recordsetclone somehow but does not seem a way.

I have wanted this before but currently..

I have a form that is based on a query but I use me.filter on it also so cannot use Dmin etc on the query. I quess I could, but don't really want to make a whole SQL to replicate the current source of the form including an filters but again just seems ott.
I could also put it into a table & DMin but again seems ott.

Is there an easy to do this?
I guess I could .sort the RS for first & last but still seems a little unnecessary and whilst quite a small RS I imagine slow'ish.

Thanks I/A

You could do a UNION query based on one of the following
1. Aggregate query with Max for field unioned with another and Min for field
2. TOP 1 query sorted in ascending order unioned with TOP 1 sorted in descending order
The results will of course be read only in either case.

If you want the records to be editable do method 2 but use 2 separate subforms each with one of the records

Hi, thanks for your reply,

not quite sure how this helps, as I say trying to avoid doing it with a query
If I went the query root it would be easy to make a ordinary query that results in the Max. I filter this form so not the same as the original query.

Also the querying of the form is already done & seems less processes taking the recordsetclone data.


Set RS = Me.RecordsetClone

Is there a way to action a Min Or Max on a field in this RS.

Thanks

How will using a recordset be better? It will likely be much slower.

Hi,

Perhaps it is not , I am no expert but guessed that once the forms is filtered by the underlying query, the resulting recordsetclone did not need to go through all the data again.

e.g.
table with 50,000 records
The forms underlying query, filters the list down to 20 with the name
e.g. SMITH

Does the recordsetclone just take those 20 records or does it have to go through the 50,000 again?

My situation is I then use me.filter to reduce the list to those with initials
e.g. B

I am then left with 4 records. I would like to know the Min & Max e.g.dates that are associated with those 4 & check just those rather than the 50,000 again.

I could have the query getting all the perimeters from a form etc but in some cases I find it easier to use filters, especially when they may be 10 or more fields that I might was to search on, just 1 or a few or all.

Only thing I can come up with:

bignose2 said:

Hi,

Perhaps it is not , I am no expert but guessed that once the forms is filtered by the underlying query, the resulting recordsetclone did not need to go through all the data again.

e.g.
table with 50,000 records
The forms underlying query, filters the list down to 20 with the name
e.g. SMITH

Does the recordsetclone just take those 20 records or does it have to go through the 50,000 again?

Click to expand...

Recordsets work by going through the data row by agonising row (RBAR). So they are very slow for large datasets

By contrast queries and SQL statements can be thousands of times faster especially if you are sorting/searching on indexed fields.
This is because they effectively operate on the entire dataset 'at once'