looping through recordsets to find the latest field value

[Rod]

Your friend's code will work but has restrictions in that there must be 10 and only 10 lot quantity columns. I have not yet opened your db but gather the columns are named r01Qty, r02Qty, etc. Where does the RLastQty column magically come from?

OK, one of my routines will work inside the SQL as I indicated. What I will do is add it to your db suitably altered for your names and then send you back your db. Copy and paste will be the crude transfer method for you to transfer it across to your development db.

My solution works with any number of lot quantity columns as long as they are consistently named. However my caveat still applies: don't use it for tables with thousands of records.

[Rod]

Here it is:

PipingLatestQty.zip

Unzip and run the query to check it's what you want.

Have a look at the query's design grid and note that the additional column specification is:

LatestQty: CalcLatestQty([Unique code])

Once you've transferred the module to your own db use this to invoke it.

I've called my module 'Common' and it has two procedures, only one of which is public. I've written it as generally as possible such that the order of the columns does not matter (as long as the naming is consistent), nor does the number of columns matter (up to 100 which is the limit for the R## Qty naming convention).

To transfer the module either export it then import it as you did before or create a new standard module in your db (any name) and copy/paste the code across.