return record id of row with largest value

[wxkeep]

Back in the saddle. Been a while since I've done any VBA coding and the software has seen some changes since last I played with it 10 years ago.

Here's what I'm trying to do (short term)
IN VBA - use a query (or DLOOKUP etc) to search a table for the row that contains the highest value that is less than a supplied value and return the row id of that row.

So, user puts in an amount (say 10,000) and the systems will find all records with a value in field "searchme" of LESS than 10,000, and then return the row id of the row with highest "searchme" less than 10,000.

LONG TERM
that returned id will become the criteria for a new query. This second query will search another table for all rows with a "category_ID" equal to the row_ID returned from the previous query.

The second step is easy - I only provide it in case you might suggest a way to do it all in one shot instead of two seperate passes.

Thanks!

 

[dhookom]

wxkeep,
Please provide actual table and field names. Otherwise all you might get is something like:

DMax("ID","A","ID < " & intSuppliedValue  )

Duane
Hook'D on Access
MS Access MVP

 

[wxkeep]

tablename: giftSizes
field to find largest value in: giftMin

The query would look something like this:

SELECT Max(giftMin), size_ID FROM giftSizes WHERE giftMin < Me.textbox.Value


textbox.value = a user field where a given gift amount is inputed. The query needs to return the row id for the row containing the CLOSEST giftsize without going over the inputed gift size.

 

[wxkeep]

Alright - at this point, I just used a DAO recordset to pull it

Dim rs As DAO.Recordset
    Dim db As Database
    Dim strSQL As String
    
    Set db = CurrentDb
    
    strSQL = "SELECT size_ID FROM giftSizes WHERE (giftMin < " & Me.gift_Amount_TxtBx.Value & ") ORDER BY giftMin DESC"
    
    Set rs = db.OpenRecordset(strSQL)

From there a reference to rs(0) gives me the result that I want. BUT if you know of an easier/faster/more efficient way to perform this same task, please advise. =)

 

[dhookom]

Your code should work. You could also use SELECT TOP 1 ... to return only a single record.

Duane
Hook'D on Access
MS Access MVP