Quick question - finding max record in query (1 Viewer)

ayden2007

Registered User.
Local time
Tomorrow, 02:04
Joined
Jul 18, 2015
Messages
13
I feel like a ninny here. Cant figure out how to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
 

Attachments

  • 26-07-2015 1-00-17 AM.jpg
    26-07-2015 1-00-17 AM.jpg
    68.3 KB · Views: 92

MarkK

bit cruncher
Local time
Today, 11:04
Joined
Mar 17, 2004
Messages
8,178
It's important, IMO, to distinguish between the Max value in a field in a table, and the record that contains that max.
To get the max value we could run a DMax(), like . . .
Code:
dim id as long
id = DMax("FooID", "tblFoo")
. . . but to get the record that contains the max, I would sort on that field, and take the first record, so . . .
Code:
SELECT TOP 1 * FROM tblFoo ORDER BY FooID DESC;
. . . and to get at that data you'd need to open a recordset. Or save it as a query (which you know will only ever contain one record) and DLookup() it, so if you saved that SQL as qryFoo . . .
Code:
dim id as long
id = DLookup("FooID", "qryFoo")
Makes sense?
 

ayden2007

Registered User.
Local time
Tomorrow, 02:04
Joined
Jul 18, 2015
Messages
13
@MarkK

Thanks for that.. figured it out and works now! Cheers
 

Users who are viewing this thread

Top Bottom